Hi there I've been given a project to bugfix a macro, but anything I try doesn't work. The general idea would be this:
There are a random number of sheets (changes depending on how many reports we are importing) and one main sheet called SUMMARY.
What I am trying to do is make it so that whenever E18 on a sheet has the string N/A in it I change the cell G23 on the Summary sheet to N/A as well. I'd want to loop through all the sheets checking if they have N/A value in their E18 cells and change cells on the Summary sheet to N/A too (as in if there is a match on Sheet2 I'd change G23 on the Summary sheet too, if there's a match on Sheet3 I'd change G24 and so on). If there is no N/A value in E18 then I'd skip it and skip a line in the G column of the Summary sheet as well.
I have the following fixed version of my code which the community generously provided that is supposed to work but It just runs without doing anything.
Dim lRow As Long
LastRow = Sheets("Summary").Cells(Sheets("Summary").Rows.Count, "G").End(xlUp).Row
lRow = 23 ' start from row 23
For Each ws In Worksheets
With ws
If .Name <> "Summary" Then
If IsError(.Range("E18").Value) Then
If .Range("E18").Value = CVErr(xlErrNA) Then
Sheets("Summary").Range("G" & lRow).Value = CVErr(xlErrNA)
End If
End If
End If
End With
lRow = lRow + 1
Next ws Any help would be greatly appreciated!
There are a random number of sheets (changes depending on how many reports we are importing) and one main sheet called SUMMARY.
What I am trying to do is make it so that whenever E18 on a sheet has the string N/A in it I change the cell G23 on the Summary sheet to N/A as well. I'd want to loop through all the sheets checking if they have N/A value in their E18 cells and change cells on the Summary sheet to N/A too (as in if there is a match on Sheet2 I'd change G23 on the Summary sheet too, if there's a match on Sheet3 I'd change G24 and so on). If there is no N/A value in E18 then I'd skip it and skip a line in the G column of the Summary sheet as well.
I have the following fixed version of my code which the community generously provided that is supposed to work but It just runs without doing anything.
Dim lRow As Long
LastRow = Sheets("Summary").Cells(Sheets("Summary").Rows.Count, "G").End(xlUp).Row
lRow = 23 ' start from row 23
For Each ws In Worksheets
With ws
If .Name <> "Summary" Then
If IsError(.Range("E18").Value) Then
If .Range("E18").Value = CVErr(xlErrNA) Then
Sheets("Summary").Range("G" & lRow).Value = CVErr(xlErrNA)
End If
End If
End If
End With
lRow = lRow + 1
Next ws Any help would be greatly appreciated!