VBA Looping for If, then else statements

[Chissa]

Hi there,
I have the below VBA code, but I need to know how to amend it so that it does the code on every line until there is nothing in the next cell? Also if the if statement is not = "Journal", then I want it to go to the next cell and do the same test.


ActiveSheet.Range("J22").Select
If ActiveCell = ("Journal") Then
Range("T22").Select
ActiveCell.FormulaR1C1 = "=RC[-2]&"" ""&RC[-1]"


Hope you can help me

Thank you

 

[Trebor76]

Hi Chissa,

Welcome to the forum!!

See how this goes:

Option Explicit
Sub Macro1()

    'http://www.mrexcel.com/forum/showthread.php?t=581471
    
    Dim lngLastRow As Long
    
    lngLastRow = Range("J" & Rows.Count).End(xlUp).Row 'Finds the last row in column J.  Change to suit.

    Range("T22:T" & lngLastRow).Formula = "=IF(J22=""Journal"",R22&"" ""&S22,"""")"

End Sub

Regards,

Robert

 

[Chissa]

Thank you sooo much. I have been trying to work on that for a week!!!! And you did it in 5 mins..as you can tell I have only just started VBA and I'm finding it a little tricky.

If I wanted to add on to that code, that if J22 doesn't = "Journal" then pull through the text from S22?

How would I go about that?

Also sorry just to clarify you code:
Range("T22:T" & lngLastRow).Formula = "=IF(J22=""Journal"",R22&"" ""&S22,"""")"

Is just saying "In the range from T3 to the last cell in row T, do the IF formula? Just so I can try and learn to get a bit better.

Thanks again

 

[Trebor76]

If I wanted to add on to that code, that if J22 doesn't = "Journal" then pull through the text from S22?

Try this:

Option Explicit
Sub Macro1()

    'http://www.mrexcel.com/forum/showthread.php?t=581471
    
    Dim lngLastRow As Long
    
    lngLastRow = Range("J" & Rows.Count).End(xlUp).Row 'Finds the last row in column J.  Change to suit.

    Range("T22:T" & lngLastRow).Formula = "=IF(J22=""Journal"",R22&"" ""&S22,S22)"

End Sub

Is just saying "In the range from T3 to the last cell in row T, do the IF formula?

No, it's starting from cell T22 and puts the formula down to the last row number that is in column J as this column is the one being referenced (I've marked the code where to change this if need be).

Robert

 

[Chissa]

Thank you very much for your help!