VBA code to return text in another cell if a cell contains value

[SamCha]

Hi all,

Please can you help me with a code which will return text "P", "W", "N" and "GB" in columns A, B, E and F respectively. (input starts from Row 5) in Sheet 2 if column B in Sheet 1 (range starting from Row 2 - Row 1 has heading) has a value. If Column B has no value the returned field in Sheet 2 in the respective columns will be empty.

Thanks in advance.

 

[Peter_SSs]

Without some sample data and expected results (preferably with XL2BB) determining what you have and what you want is very much a guess so I'm expecting the following to be incorrect but it might get the ball rolling.

Test with a copy of your workbook.

Sub Check_for_Values()
  Dim Result() As String, ResultCols() As String
  Dim i As Long, Rw As Long, col As Long, Cols As Long
  
  Result = Split("P W N GB")
  ResultCols = Split("A B E F")
  Cols = UBound(ResultCols) + 1
  With Sheets("Sheet1")
    For i = 2 To .Range("B" & Rows.Count).End(xlUp).Row
      If Len(.Range("B" & i).Value) > 0 Then
        Rw = 5 + Int((i - 2) / Cols)
        col = (i - 2) Mod Cols
        Sheets("Sheet2").Cells(Rw, ResultCols(col)).Value = Result(col)
      End If
    Next i
  End With
End Sub

 

[SamCha]

Without some sample data and expected results (preferably with XL2BB) determining what you have and what you want is very much a guess so I'm expecting the following to be incorrect but it might get the ball rolling.

Test with a copy of your workbook.

Sub Check_for_Values()
  Dim Result() As String, ResultCols() As String
  Dim i As Long, Rw As Long, col As Long, Cols As Long
 
  Result = Split("P W N GB")
  ResultCols = Split("A B E F")
  Cols = UBound(ResultCols) + 1
  With Sheets("Sheet1")
    For i = 2 To .Range("B" & Rows.Count).End(xlUp).Row
      If Len(.Range("B" & i).Value) > 0 Then
        Rw = 5 + Int((i - 2) / Cols)
        col = (i - 2) Mod Cols
        Sheets("Sheet2").Cells(Rw, ResultCols(col)).Value = Result(col)
      End If
    Next i
  End With
End Sub

Hi Peter,

The code is working to some extend, however, I have missed in saying that Sheet 1 is a filtered sheet - Column B is the filtered column and only the filtered ID is copied to the Sheet 2. When I ran this code the resp columns were populated by the text P, W, N and GB but it went down to row 30 instead of just Row 5 as there is only one entry filtered for that cus no. selected in Sheet 1.

 

[Peter_SSs]

The code is working to some extend, however, I have missed in saying that Sheet 1 is a filtered sheet

Are you saying that the code could be correct provided it only looked at the visible rows?

 

[Peter_SSs]

Actually, I don't think my previous question will resolve the problem.
As hinted at before could we have ..

some sample data and expected results (preferably with XL2BB)

(I cannot copy from a picture to test)

 

[SamCha]

Are you saying that the code could be correct provided it only looked at the visible rows?

Yes Peter. Thats what it seemed to be.

 

[Peter_SSs]

Yes Peter. Thats what it seemed to be

Post #5 still stands as I am not quite sure how the final layout would be affected by filtered rows.