VBA Loops

jeskit · May 3rd, 2011, 05:44 AM

Hi

I have a loop which loops through a cell range and checks multiple things and returns results in multiple columns within a another sheet.

Code:

Nrow = 2

For Each cell In Sheets("PPIIIFORM").Range("F4:U644")
    If cell.Value <> "" Then Sheets("Input_Reference_Table").Cells(Nrow, 1).Value = cell.Value
    Sheets("Input_Reference_Table").Cells(Nrow, 12).Value = Not cell.Interior.Color = RGB(217, 217, 217)
    Sheets("Input_Reference_Table").Cells(Nrow, 14).Value = cell.Interior.Color = RGB(255, 255, 0)
    Sheets("Input_Reference_Table").Cells(Nrow, 11).Value = cell.Column
    If Right(cell.NumberFormat, 1) = "%" Then
        Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10.0f%%"
    ElseIf Right(cell.NumberFormat, 1) = "0.000%" Then
       Sheets("Input_Reference_Table").Cells(Nrow, 8).Value = ParseNFmt(cell.NumberFormat)
    ElseIf Right(cell.NumberFormat, 1) = "0.000" Then
       Sheets("Input_Reference_Table").Cells(Nrow, 8).Value = ParseNFmt2(cell.NumberFormat)
    Else
        Sheets("Input_Reference_Table").Cells(Nrow, 9).Value = "%10.0f%"
    End If
    Nrow = Nrow + 1
    Application.StatusBar = cell.Address
Next cell

I need add a line of code to the above code so for each cell its returns in the column H in sheet Input_Reference_Table "(the value in corresponding cell in column E) & " - " & (first row of the corresponding column)"

However i am not sure how to do this in a loop, Does anyone know?

Thanks

Jeskit

jeskit · May 3rd, 2011, 05:58 AM

Hi

i had a go at writing the line of code to add to my loop but it doesn't brng back the cell value for example it brings back "E6 - T1"

Code:

    Sheets("Input_Reference_Table").Cells(Nrow, 8).Value = "E" & cell.Row & " - " & Mid("ABCDEFGHIJKLMNOPQRSTUVWXYZ", cell.column, 1) & "1"

jeskit · May 3rd, 2011, 06:22 AM

Hi

No worries i have figured it out!!!

Code:

Sheets("Input_Reference_Table").Cells(Nrow, 8).Value = Range("E" & cell.Row).Value & " - " & Range(Mid("ABCDEFGHIJKLMNOPQRSTUVWXYZ", cell.column, 1) & "1").Value