Hi,
I have made a stock sheet that uses a for macro:
For i = 11 To 20
Range("l" & i).Select
If ActiveCell.Value > "" Then
Range("start_mail").Select
Else
GoTo endOfLoop
End If
If Range("start_mail").Value = "" Then
ActiveCell.Value = Range("l" & i) & " x " & Range("f" & i) & ", " & " product code " & Range("g" & i) & " (" & Range("e" & i) & ", " & Range("c" & i) & ")"
ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate
ActiveWorkbook.Names.Add Name:="start_mail", RefersTo:=ActiveCell
End If
endOfLoop:
Next i
I would like to know if it is possible to place a loop before the code which will run down the list of products [these are in D11 down] untill it finds an empty cell, when it stops on the empty cell I want it to take the cell number eg 30 I dont want the cell letter, and place this as a variable [CellCount] this will then be used in the for so it will look like this:
For i = 11 To CellCount
this is so when new products are added I wont need to change the code manually to pick it up.
So far I have:
Range("d11").Select
With ActiveCell
Do
If ActiveCell.Value <> "" Then
ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate
Else
Exit Sub
End If
Loop Until IsEmpty(ActiveCell)
End With
Please can you help, everthing I try picks up the Cell Reference er d30, I just want it to use the 30.
Thanks
I have made a stock sheet that uses a for macro:
For i = 11 To 20
Range("l" & i).Select
If ActiveCell.Value > "" Then
Range("start_mail").Select
Else
GoTo endOfLoop
End If
If Range("start_mail").Value = "" Then
ActiveCell.Value = Range("l" & i) & " x " & Range("f" & i) & ", " & " product code " & Range("g" & i) & " (" & Range("e" & i) & ", " & Range("c" & i) & ")"
ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate
ActiveWorkbook.Names.Add Name:="start_mail", RefersTo:=ActiveCell
End If
endOfLoop:
Next i
I would like to know if it is possible to place a loop before the code which will run down the list of products [these are in D11 down] untill it finds an empty cell, when it stops on the empty cell I want it to take the cell number eg 30 I dont want the cell letter, and place this as a variable [CellCount] this will then be used in the for so it will look like this:
For i = 11 To CellCount
this is so when new products are added I wont need to change the code manually to pick it up.
So far I have:
Range("d11").Select
With ActiveCell
Do
If ActiveCell.Value <> "" Then
ActiveCell.Offset(rowoffset:=1, columnoffset:=0).Activate
Else
Exit Sub
End If
Loop Until IsEmpty(ActiveCell)
End With
Please can you help, everthing I try picks up the Cell Reference er d30, I just want it to use the 30.
Thanks