Macro for IF THEN conditions with Dynamic row range

**rsami** · 07-05-2013, 07:54 AM

hello,

i need to automate some manual works using Macro.
here i have attached the relevant excel sheet : if conditions.xlsx

here number of rows are not a fixed,it may change day by day and i have to run macro every day.since macro should identify the number of rows (that's the most difficult part for me with my little macro knowledge)
let assume that row index is i (ith row)

if Bi="IH"
then
Ii=Si , Ji=Ti ,Ki=Ui,Li=Vi ,Mi=Wi , Ni=Xi ,Oi=Yi

if (date of Ai= date of Bi)
then
Ii=Si , Ji=Ti ,Ki=Ui,Li=Vi ,Mi=Wi , Ni=Xi ,Oi=Yi

if Bi= "Transfer"
then
Ii=Si , Ji=Ti ,Ki=Ui,Li=Vi ,Mi=Wi , Ni=Xi ,Oi=Yi

if Bi= "Hold" or "Update next week"
then
Ii=Ji=Ki=Li="TBA"

if Ai="Update next week" and Bi=Di
then
Ii=Ji=Ki=Bi and Li="Achievable"

else

I,J,K,L,M,N,O should be empty cells(no need to change )

hope i will get a help from you to overcome this problem
thanks very much

**JasperD** · 07-05-2013, 08:46 AM

Hi rsami,

try this :

Sub test()
Dim cell As Range, cr%

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.EnableEvents = False


For Each cell In Range("B3:B" & Range("B65536").End(xlUp).Row)
cr = cell.Row

If cell.Value = "IH" Or cell.Value = "Transfer" Or cell.Offset(0, -1).Value = cell.Value Then
Range("I" & cr).Value = Range("S" & cr).Value
Range("J" & cr).Value = Range("T" & cr).Value
Range("K" & cr).Value = Range("U" & cr).Value
Range("L" & cr).Value = Range("V" & cr).Value
Range("M" & cr).Value = Range("W" & cr).Value
Range("N" & cr).Value = Range("X" & cr).Value
Range("O" & cr).Value = Range("Y" & cr).Value
GoTo nxt
End If

If cell.Value = "Hold" Or cell.Value = "Update next week" Then
Range("I" & cr & ":L" & cr).Value = "TBA"
GoTo nxt
End If

If Range("A" & cr).Value = "Update next week" And cell.Value = Range("D" & cr).Value Then
Range("I" & cr & ":K" & cr).Value = cell.Value
Range("L" & cr).Value = "Achievable"
GoTo nxt
End If

Range("I" & cr & ":O" & cr).Value = ""
 
nxt:
Next cell

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True

End Sub

Please click the * below if this helps