Not able to get the cell value in foreach loop using excel vba

Question

enter image description here

Hi, I have enclosed the sheet image. My requirement is: I want to get all the "G" column values for the organization matching to a specific organization name (Ex:360 evaluations).

I am getting null value after first loop for the G Column

Sub UsageWeekTrend()

    Dim customerName As String
    Dim sheetName As String
    Dim dataFound As Boolean

    Dim selectedCell As Range
    Dim rowNumber As Integer
    Dim weekMinutes As Double

    Dim trendsFile As Workbook
    Dim trendsSheet As Worksheet

    On Error GoTo errorHandling

    sheetName = ActiveSheet.Name
    customerName = ActiveSheet.Range("A" & (ActiveCell.row)).Value
    dataFound = False

    For Each selectedCell In ActiveSheet.Range("A1:A1000")
     If UCase(selectedCell.Value) = UCase(customerName) Then
        weekMinutes = ActiveSheet.Range("G" & selectedCell.row).Value
        Debug.Print weekMinutes
        Debug.Print "G" & selectedCell.row

           If dataFound = False Then

                  If trendsFile Is Nothing Then
                     Set trendsFile = Workbooks.Add()
                     trendsFile.Activate
                     Set trendsSheet = trendsFile.ActiveSheet

                    Else
                       ' add a new sheet to the trends workbook
                        trendsFile.Activate
                        Set trendsSheet = Sheets.Add

                    End If

                dataFound = True
                rowNumber = 1
                trendsSheet.Name = Left(customerName, 10) + " " + Format(Date, "MMDD")
                trendsSheet.Cells(rowNumber, 1) = "Users"
                trendsSheet.Cells(rowNumber, 2) = "Minutes"
                rowNumber = rowNumber + 1

            End If

             ' if a sheet has been created, then we have at least one non-zero value so add data

             If dataFound = True Then
                trendsSheet.Cells(rowNumber, 1) = customerName
                trendsSheet.Cells(rowNumber, 2) = weekMinutes
                rowNumber = rowNumber + 1
             End If
       End If

  Next selectedCell

    ' if we have data, create the chart

    If dataFound = True Then

        ' make sure the trends sheet is active for chart insertion

        trendsSheet.Activate

        Dim chtChart As ChartObject
        Dim chartName As String
        Dim endRange As String

        ' define the end of the range for the chart

        endRange = "C" & CStr(rowNumber - 1)

        ' add chart to current sheet

        Set chtChart = ActiveSheet.ChartObjects.Add(Left:=200, Top:=200, Width:=900, Height:=400)
        chtChart.Activate
        ActiveChart.ChartType = xlLineStacked
        ActiveChart.SetSourceData Source:=trendsSheet.Range("A2", endRange)
        ActiveChart.HasTitle = True
        ActiveChart.ChartTitle.Text = customerName
        ActiveChart.ApplyLayout (5)


    Else
        MsgBox ("No usage data found for customer " + customerName)
    End If

    Exit Sub

errorHandling:
    MsgBox (Err.Description)


End Sub

Code works fine with test data, check if your columns are formated — Kᴀτᴢ, May 06 '15 at 12:34
@katz I would strongly disagree, see what both Sobigen & I wrote — FreeMan, May 06 '15 at 12:43
@FreeMan sry you're right, I activated workbook for debugging so it worked — Kᴀτᴢ, May 06 '15 at 12:48

score 2 · Answer 1 · edited May 23 '17 at 12:30

When you run this line:

trendsFile.Activate

You change the Activesheet, so the 2nd time on the loop you again look at the activesheet

weekMinutes = ActiveSheet.Range("G" & selectedCell.row).Value

but the activesheet has changed. I would change those Activesheet calls to a worksheet object that you assign at the top.

This is always a good read for those new to VBA programming: How to avoid using Select in Excel VBA macros

score 1 · Answer 2 · answered May 06 '15 at 12:42

The issue is that you're using ActiveSheet, and the active sheet is being changed in your code.

As soon as trendsFile.Activate is executed, these two references will have new meanings ActiveSheet.Range("A1:A1000") and ActiveSheet.Range("G" & selectedCell.row).Value.

You've created workbook & worksheet variables for your Trends file, and use those, you also need to create a worksheet variable for your "source" worksheet (not sure how you'd refer to it).

Also, I'd be a bit concerned about this section of code:

If trendsFile Is Nothing Then
  Set trendsFile = Workbooks.Add()
  trendsFile.Activate
  Set trendsSheet = trendsFile.ActiveSheet
Else
  ' add a new sheet to the trends workbook
  trendsFile.Activate
  Set trendsSheet = Sheets.Add
End If

I believe you'll be adding a new sheet every time through the loop.

Try something like this:

Sub UsageWeekTrend()

    Dim customerName As String
    Dim sheetName As String
    Dim dataFound As Boolean

    Dim selectedCell As Range
    Dim rowNumber As Integer
    Dim weekMinutes As Double

    Dim trendsFile As Workbook
    Dim trendsSheet As Worksheet
    Dim SourceSheet as worksheet 'this is the place where  you start, call it what you will 

    On Error GoTo errorHandling

    set SourceSheet = activesheet 'this will now always be THIS sheet, and won't change
    sheetName = SourceSheet.Name
    customerName = SourceSheet.Range("A" & (ActiveCell.row)).Value
    dataFound = False

    For Each selectedCell In SourceSheet.Range("A1:A1000")
     If UCase(selectedCell.Value) = UCase(customerName) Then
        weekMinutes = SourceSheet.Range("G" & selectedCell.row).Value
        Debug.Print weekMinutes
        Debug.Print "G" & selectedCell.row
        If dataFound = False Then
           If trendsFile Is Nothing Then
              Set trendsFile = Workbooks.Add()
              'trendsFile.Activate   - never needed
              Set trendsSheet = trendsFile.Sheets("Sheet1") 'use the first sheet, since you just created a brand new workbook
            Else
              ' add a new sheet to the trends workbook
              'trendsFile.Activate   -- you never need this when you're working with an object instead of "Active"
      'you'll find that this line will add a new sheet every time you execute the loop
      'once you've created your "trendsFile" workbook. you'll need to do some tweaking here
      'to prevent you getting one loop worth of data on each sheet
              Set trendsSheet = Sheets.Add
           End If
           dataFound = True
           rowNumber = 1
           trendsSheet.Name = Left(customerName, 10) + " " + Format(Date, "MMDD")
           trendsSheet.Cells(rowNumber, 1) = "Users"
           trendsSheet.Cells(rowNumber, 2) = "Minutes"
           rowNumber = rowNumber + 1
        End If

        ' if a sheet has been created, then we have at least one non-zero value so add data
        If dataFound = True Then
                trendsSheet.Cells(rowNumber, 1) = customerName
                trendsSheet.Cells(rowNumber, 2) = weekMinutes
                rowNumber = rowNumber + 1
        End If
     End If
  Next selectedCell

'The rest of your routine here...    

End Sub

Not able to get the cell value in foreach loop using excel vba

2 Answers2