Insert Value into Cell

[acemecca]

Greetings:

I am new to VBA and need assistance badly. I am working a spreadsheet where I need to locate the number '3' in column B and populate the number '2' in the cell above it in column B.

Please advise...thanks!

 

[VoG]

Try like this

Sub b()
Dim Found As Range
Set Found = Columns("B").Find(what:=3, LookIn:=xlValues, lookat:=xlWhole)
If Not Found Is Nothing Then Found.Offset(-1).Value = 2
End Sub

 

[acemecca]

how do you make the macro repeat for the entire spreadsheet? I worked on one line but not the entire spreadsheet...thanks!

 

[VoG]

Try

Sub b()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    If Range("B" & i).Value = 3 Then Range("B" & i - 1).lvalue = 2
Next i
End Sub

 

[acemecca]

I am getting a run-time error 438...i deleted the first macro and this is the error i am receiving...you are helping me a GREAT deal...thanks for the assistance...looking forward to your reply!

 

[VoG]

Sorry, I had a typo

Sub b()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    If Range("B" & i).Value = 3 Then Range("B" & i - 1).Value = 2
Next i
End Sub

 

[acemecca]

PERFECT....and what do I substitute for locating the number 2 and populating the number 1 in the same spreadsheet??

 

[VoG]

That would be

Sub b()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 2 To LR
    If Range("B" & i).Value = 2 Then Range("B" & i - 1).Value = 1
Next i
End Sub

 

[acemecca]

Peter - u are a lifesaver....so far so good on my build....I will bug you later but this is a great start....thanks again!

 

[acemecca]

Actually, I have one more thing before I can start filling my template....how can I copy a formula (ie. located in C2) and populate or paste that in column C where the number 2 is present in column B? I hope I explained it right. I will use this for another formula to apply to column B's number 1 entries....thanks!