Vba code to update a cell value based on its current value and another cells value

Hello Excel Forum--this is my first post on this site although I've used this site many times. I need a code that can find a specific value in a specific column and once that value is found, look at another column in the same row for a specific value. If both values are found, update the first value found with a specific value. For example;

I have data filled in all cells range (A1:C100). Look in Column A for the value "Apples", Say "Apples" is the value of cell A13. Then look at C13 to see if it has the value of "005". If it does not, then do nothing. If it does, then update cell A13 with the value "Apples 005". Then continue the process through the end of Column A.

Here is the code I have tried to work with:

Sub Test()

Set target = Sheets("Sheet1").Range("A:A")
Set target1 = Sheets("Sheet1").Range("C:C")

For Each cell In target1

If cell.Value = "005"

Then

For Each cell In target

If cell.Value = "Apples"
cell.Value = "Apples 005"

End If

Next cell




End Sub


I know how to make this code work for one condition but not for two conditions. I have similar restriction issues with the following code:


Sub Test1()



If Range("A13").Value = "Apples" And Range("C13").Value = "005" Then
Range("A13").Value = "Apples 005"

End If

End Sub

This is good if I always knew the location of the value of "Apples", but I do not. I only know the Column and value. Any help on this will be much appreciated, as I search this site for similar issues but have not found any. Also, I can make this work via a formula but I prefer a to stick with a VBA macro.


Thank you!

You could use the "Find" method:

Sub Snowbird(): Dim F As Range, A As Range
Set A = Range("A:A")
Set F = A.Find("Apples")
If Not F Is Nothing Then
If F.Offset(0, 2) Then
F = F & " " & F.Offset(0, 2)
End If: End If: End Sub

Consider:

Sub Test()
Dim t As Range, t1 As Range, cell As Range
Set t = Intersect(Sheets("Sheet1").UsedRange, Sheets("Sheet1").Range("A:A"))
For Each cell In t
    If cell.Value = "Apples" And cell.Offset(0, 2).Text = "005" Then
        cell.Value = "Apples 005"
    End If
Next cell
End Sub

1. we only loop over an area of column A that may have data in it
2. we refer to column C values as an offset from column A

Yeah, the "Find" method is often combined with the FindNext. I'll write some code for that for my next post

Hi g.h0st.snwbrd3r:

Get a good book (used if possible) and start working the examples. If you can work with Java, VBA should not be difficult to pick.

Hi Snowbird,

You can learn much by just reading code - I learned BASIC in Graduate School, and I taught BASIC in a Community College in the early 80's. Otherwise, I taught myself the Excel VBA using Steven Roman's "Writing Excel Macros" published by O'Reilly. Another good book is the "Excel (Year of your choice) Power Programming" by John Walkenbach published by Wiley -
which can be had from Amazon for about $25.

Here's one way of adding the FindNext to your Finder:

Sub Snowbird(): Dim F As Range, A As Range, r As Long
Set A = Range("A:A")
Set F = A.Find("Apples")
If Not F Is Nothing Then
If F.Offset(0, 2) Then
    F = F & " " & F.Offset(0, 2)
        r = F.row: End If
    Do
            Set F = A.FindNext(F)
    If F Is Nothing Or F.row <= r Then Exit Sub
        If F.Offset(0, 2) Then
            F = F & " " & F.Offset(0, 2)
                    r = F.row: End If
    Loop While Not F Is Nothing
End If: End Sub

You may as well start answering questions now - you'll learn more that way. And, you're welcome!