VBA Find String in Range, Copy Cell in Same Row, Paste into Adjacent Cell

[MattieP]

Hi everyone,

I've been trying to get this problem solved for a bit, and I keep feeling like I am over thinking it. I've searched a few different ways on the forums and tried to combine a few different types of codes together, but keep getting errors. From what I have gathered so far is that I am looking to find a specific string (that shows up multiple times) in column I, and if it exists in that cell, then take the cell in the same row in column A, and paste it into column B with an "N5" in front of it (which I also can't seem to find how to add into this). If it doesn't have this string in the cell, then do nothing. My sheet is a table that starts in row 4 (with headers in row 3), but I don't think I am needing to change anything because of that, but I can't seem to get this. Currently my code is below. Any help would be amazing.

    Dim li As Long    Dim i As Integer
    Dim N1 As String
    N1 = "ABCD"
    li = Worksheets("Sheet1").Range("I" & Rows.Count).End(xlUp).Row
    For i = 1 To li
    If UCase(Worksheets("Sheet1").Cells(i, 9).Value) = N1 Then
        Worksheets("Sheet1").Cells(i, 2).Value = Worksheets("Sheet1").Cells(i, 1).Offset(, 1).Value

    End If
    Next i

As a reminder, if column I has "ABCD" in the cell, then on the same row, take column A and paste it into column B with "N5" in front of it (ie. column A is 123, and this if statement is true, then column B cell would be "N5123"). Please let me know if that makes sense, or if I am missing any information. Thanks!

 

[Akuini]

Hi everyone,

As a reminder, if column I has "ABCD" in the cell, then on the same row, take column A and paste it into column B with "N5" in front of it (ie. column A is 123, and this if statement is true, then column B cell would be "N5123"). Please let me know if that makes sense, or if I am missing any information. Thanks!

If I understand you correctly:
if col B has an "N5" in front of it then copy-paste value col A to B and add an "N5" in front of it.
This is untested:

With Worksheets("Sheet1")
If UCase(.Cells(i, 9).Value) = N1 And Left(.Cells(i, 2).Value, 2) = "N5" Then
        .Cells(i, 2).Value = "N5" & .Cells(i, 1).Value
End If
End With

 

[MattieP]

If I understand you correctly:
if col B has an "N5" in front of it then copy-paste value col A to B and add an "N5" in front of it.
This is untested:

With Worksheets("Sheet1")
If UCase(.Cells(i, 9).Value) = N1 And Left(.Cells(i, 2).Value, 2) = "N5" Then
        .Cells(i, 2).Value = "N5" & .Cells(i, 1).Value
End If
End With

Close, If col I has "ABCD" as the entire cell contents, then take col A and paste it into col B with an "N5" in front of it. Looking at your code, it makes total sense up to the "And Left" where I wouldn't need that (since "N5" doesn't exist until it finds "ABCD"). Also, after trying to retrofit the code and removing that, I got it to work, but by instead of placing the Next i inside of the End With, I placed it outside. Here is what the code is now:

    Dim li As Long    Dim i As Integer
    Dim N1 As String
    N1 = "ABCD"
    li = Worksheets("Sheet1").Range("I" & Rows.Count).End(xlUp).Row
    For i = 1 To li


    With Worksheets("Sheet1")
    If UCase(.Cells(i, 9).Value) = N1 Then
        .Cells(i, 2).Value = "N5" & .Cells(i, 1).Value
    End If
    End With
    Next i

Akuini, you have save the day and some of my sanity. Thanks so much!

 

[Akuini]

Close, If col I has "ABCD" as the entire cell contents, then take col A and paste it into col B with an "N5" in front of it. Looking at your code, it makes total sense up to the "And Left" where I wouldn't need that (since "N5" doesn't exist until it finds "ABCD"). Also, after trying to retrofit the code and removing that, I got it to work, but by instead of placing the Next i inside of the End With, I placed it outside. Here is what the code is now:

Akuini, you have save the day and some of my sanity. Thanks so much!

You're welcome, glad you figured it out.

I misunderstood the "N5" part, I thought "N5" already exists in col B & that's the requirement.