none
Facing issue with for each loop in excel VBA RRS feed

  • Question

  • Hi,
    I am using VBA .
    I am looping through each row and the column matches value,I am getting the value of  G column with the selected row.

          Dim selectedCell As Range
            Dim weekMinutes As Double
            Dim rowNumber As Integer
            
            usageTracking.Activate
            weeklySheetName = sheetName
            
             For Each selectedCell In ActiveSheet.Range("A:A")
              If selectedCell.Value = customerName Then
        '          weekMinutes = ActiveSheet.Range("G" & (selectedCell.row)).Value
                   weekMinutes = Cells(selectedCell.row, "G").Value
        End If
        
          Next selectedCell

    I am able to get value for the first row.
    from second row,even though i am having different value,the "weekMinutes" value is showing as 0 instead of the value in the cell.

    whats wrong I am doing.
    Thursday, April 30, 2015 2:27 PM

Answers

  • I tried with the below code, and it started working

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

     Dim selectedCell As Range
     Dim weekMinutes As Double

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

     For Each selectedCell In sheetName.Range("A1:A1000")
     If UCase(selectedCell.Value) = UCase(customerName) Then
     weekMinutes = sheetName.Range("G" & selectedCell.Row).Value

    Wednesday, May 6, 2015 12:57 PM

All replies

  • if you select a range (A1:A5) by clicking cell A1 and dragging to A5; cell A1 is the selected cell; it does not change.
    Thursday, April 30, 2015 8:42 PM
  • Hi AjayChigurupati,

    The code seems ok, and I also made a quick test. It works well for me.

    Please check the value in A column, the weekMinutes variable only was assigned when the value in column A is equal customerName.

    I also suggest that you loop the UsedRange in Column A to improve the preformance.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, May 1, 2015 3:24 AM
    Moderator
  • Hi

    Thanks for the reply

    I have attached a screen shot of my excel.

    I am having different customerNames in the work sheet.I am assigning the customer name initially one. I am looping thru the cells for that customer name,and if it matches i should get the value in the "G column"



    Tuesday, May 5, 2015 10:29 AM
  • Hi AjayChigruupati,

    Thanks for the detail exlaintation, however I am not able to understand the secnario exactly. Based on the code, it would get the value at last row of the custom name. If you want to get all values you need add code to handle the value when the value was finded everytime. Here is an samlpe for your reference:

    Dim selectedCell As Range
             Dim weekMinutes As Double
             Dim rowNumber As Integer
             
             usageTracking.Activate
             weeklySheetName = sheetName
             
              For Each selectedCell In ActiveSheet.Range("A:A")
               If selectedCell.Value = customerName Then
         '          weekMinutes = ActiveSheet.Range("G" & (selectedCell.row)).Value
                    weekMinutes = Cells(selectedCell.row, "G").Value
    
    'add code here to  handle the value finded
         End If
         
           Next selectedCell


    Based on my understanding, the recommnd way to find specific information in a range is using Range.Find. And here is an examlpe finds all cells in the range A1:A500 on worksheet one that contain the value 2 and changes it to 5:

    With Worksheets(1).Range("a1:a500") 
        Set c = .Find(2, lookin:=xlValues) 
        If Not c Is Nothing Then 
            firstAddress = c.Address 
            Do 
                c.Value = 5 
                Set c = .FindNext(c) 
            Loop While Not c Is Nothing And c.Address <> firstAddress 
        End If 
    End With

    If I misunderstood, please feel free to let me know.

    Regards & Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, May 6, 2015 6:47 AM
    Moderator
  • Hi Xue,

    Thanks for the reply.

    I want to have the value of G column.

    The scenario is:

    in the work sheet,i want to get all  the G column values based on the condition like the organization column(A) should match to a particular organization name  that we pass.

     For ex:

    In the image shown above, I want get the values in  "G" column whose organization name is "360 Evaluations".

    Wednesday, May 6, 2015 8:35 AM
  • I tried with the below code, and it started working

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

     Dim selectedCell As Range
     Dim weekMinutes As Double

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

     For Each selectedCell In sheetName.Range("A1:A1000")
     If UCase(selectedCell.Value) = UCase(customerName) Then
     weekMinutes = sheetName.Range("G" & selectedCell.Row).Value

    Wednesday, May 6, 2015 12:57 PM