Trying to loop through multiple worksheets and check cell values.

Rhyfelwr

New member
Joined
Aug 28, 2017
Messages
16
Reaction score
0
Points
0
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!
 
I'd want to loop through all the sheets checking if they have N/A value in their E18 cells
The verbage would indicate N/A will be text but the posted code is looking for an error result of a formula.
Can you confirm what will be in the E18 cells ?

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
Are these the sheet names shown on the tabs, there location in the tab line up, or the default codenames Excel gives to the sheets ?

Please provide links to cross postings you have of this on other forums.
 
Hi it is a String, sorry I wasn't clear on that. There is a random number of sheets (it generates when we import report files with the macro). I have attached a desensitized version of the import macro, sadly I cannot attach report files as they cannot be desensitized. You can see the template form on the second sheet.

What I am trying to do is that whenever in the imported report files (which are fixed forms) the E18 cell contains the string "N/A" I'd change the corresponding cell (named: Escalation Evidence Saved) to the string "N/A" as well on the Summary sheet, as now it shows up as 0% instead which distorts the results.

Also there's a link to the StackOverflow question: https://stackoverflow.com/questions...7325372?noredirect=1#comment81625705_47325372
 

Attachments

  • Test Case_Merger_v02_desensitized.xlsm
    53.1 KB · Views: 20
@Rhyfelwr
Please wrap code with code tags. Check the FAQ for info - Thx
 
I recommend using Option Explicit as first line of code in a Module. In Debug menu, click Compile before the first Run.

Code:
Sub NAFix()
  Dim lRow As Long, ws As Worksheet
  
  'lRow = 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 .Range("E18").Value = "N/A" Then
          'Sheets("Summary").Range("G" & lRow).Value = CVErr(xlErrNA)
          Sheets("Summary").Range("G" & lRow).Value = "N/A"
        End If
        lRow = lRow + 1
      End If
    End With
  Next ws
End Sub
 
Back
Top