Results 1 to 5 of 5

Thread: Trying to loop through multiple worksheets and check cell values.

  1. #1

    Trying to loop through multiple worksheets and check cell values.



    Register for a FREE account, and/
    or Log in to avoid these ads!

    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 AsLong

    LastRow
    = Sheets("Summary").Cells(Sheets("Summary").Rows.Count,"G").End(xlUp).Row
    lRow
    =23' start from row 23

    ForEach 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)
    EndIf
    EndIf
    EndIf
    EndWith
    lRow
    = lRow +1
    Next ws Any help would be greatly appreciated!

  2. #2
    Magician NoS's Avatar
    Join Date
    Jan 2013
    Location
    British Columbia
    Posts
    792
    Articles
    0
    Excel Version
    Excel 2010
    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.

  3. #3
    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/...25705_47325372
    Attached Files Attached Files

  4. #4
    Administrator Pecoflyer's Avatar
    Join Date
    Oct 2011
    Location
    Brussels Belgium
    Posts
    1,706
    Articles
    0
    Excel Version
    2010 on Xubuntu
    @Rhyfelwr
    Please wrap code with code tags. Check the FAQ for info - Thx
    Thank you Ken for this secure forum.

  5. #5
    Conjurer Kenneth Hobson's Avatar
    Join Date
    Mar 2014
    Location
    Tecumseh, OK
    Posts
    142
    Articles
    0
    Excel Version
    365
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •