Excel: I want to populate Column A with the Contents of Column B, only if Column B value starts with F

Question

I have two columns with different values; A and B, if the values of column B start with letter F, I want that value (the one starting with F) to replace the current value in column A. This is the VBA code I have so far, not sure if I am heading in the correct direction.

Private Sub UpdateColumnA()
Dim x As Long
For x = A To Z
    If InStr(F, Sheet1.Range("$B$" & x), "thisvalue) > 0 Then
        Sheet1.Range("$A$" & x) = Sheet1.Range("$A$" & x) & "sometext"
    End If
Next
End Sub

Also, I am not sure what content should be placed in pieces of code "thisvalue" and "sometext"

So what you are saying is that you found this code somewhere. You know it should do roughly what you want but do not know how to change it to do exactly what is desired and have come to us to rewrite it for you? I would be careful using any code you do not understand. — Scott Craner, Aug 22 '16 at 14:14
@ScottCraner I found some code somewhere, edited it to the best of my ability and came here for further help. — Z. Finley, Aug 22 '16 at 14:38
Well lucky for you the changes were fairly simple. Please realize that Stack Overflow is for specific problems to non working code. IMO, As your question stands it should have been closed for being too broad. There is not even a question in the verbiage. Next time please be more specific with the problem and ask a specific question. As it is I am glad you found an answer for your non specific question. Normally one would tell us what they want AND what is not working. Had this been longer code it would have been down voted and closed. — Scott Craner, Aug 22 '16 at 14:44

score 0 · Accepted Answer · answered Aug 22 '16 at 14:14

0

Private Sub UpdateColumnA()
Dim x As Long
lastRow = Sheet1.Cells(Sheet1.Rows.Count, 2).End(xlUp).Row
For x = 1 to lastRow
    If Left(Sheet1.Range("B" & x), 1)= "F" Then Sheet1.Range("A" & x) = Sheet1.Range("B" & x)
Next
End Sub

answered Aug 22 '16 at 14:14

Abe Gold

2,307
17
29

score 0 · Answer 2 · answered Aug 22 '16 at 14:18

Private Sub UpdateColumnA()
Dim x As Long
With Sheet1
    For x = 1 To .Cells(.Rows.Count , 2).End(xlUp).Row
        If UCase(Left(.Cells(x, 2), 1)) = "F" Then
            .Range("$A$" & x) = .Range("$B$" & x)
        End If
    Next x
End With
End Sub

Excel: I want to populate Column A with the Contents of Column B, only if Column B value starts with F

2 Answers2