Freddie has attended:
Excel VBA Introduction course
IF Function
Hi Guys
Are you able to help with the below?
The formula only works for one cell but i only want it to work for every cell that is populated in the column before the range - that is activecell.offset(0,-1). Where have i misplaced the ActiveCell.Offset(1, 0).Select?
Range("O2").Select
If ActiveCell.Offset(0, -1) <= 1 Then
ActiveCell.Value = "Overnight"
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.Offset(0, -1) = ""
Loop
ElseIf ActiveCell.Offset(0, -1) <= 7 Then ActiveCell.Value = "1 week"
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.Offset(0, -1) = ""
Loop
ElseIf ActiveCell.Offset(0, -1) <= 31 Then ActiveCell.Value = "1 month"
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.Offset(0, -1) = ""
Loop
ElseIf ActiveCell.Offset(0, -1) >= 92 Then ActiveCell.Value = "3 months"
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.Offset(0, -1) = ""
Loop
Else: ActiveCell.Value = "More than 6 months"
ActiveCell.Offset(1, 0).Select
ActiveCell.Offset(1, 0).Select
Do Until ActiveCell.Offset(0, -1) = ""
Loop
End If
THanks
Freddie
RE: IF Function
Hi Freddie,
Thank you for your post. Please have a look at my code below which seems to work.
Range("o2").Select
Do Until ActiveCell.Offset(0, -1) = ""
If ActiveCell.Offset(0, -1) <= 1 Then
ActiveCell.Value = "Overnight"
ElseIf ActiveCell.Offset(0, -1) <= 7 Then
ActiveCell.Value = "1 week"
ElseIf ActiveCell.Offset(0, -1) <= 31 Then
ActiveCell.Value = "1 month"
ElseIf ActiveCell.Offset(0, -1) >= 92 Then
ActiveCell.Value = "3 months"
Else: ActiveCell.Value = "More than 6 months"
End If
ActiveCell.Offset(1, 0).Select
Loop
It's best to use just one loop when using an If Statement such as this. It lets VBA allocate the correct result from the choices before moving to the next record.
I hope this helps.
Kind regards
Marius Barnard
STL
