is this possible

Andy W

Board Regular
Joined
Jul 31, 2003
Messages
68
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
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
Using your "So far" code you can get the row number like this:

CellCount = ActiveCell.Offset(-1,0).Row

You can avoid looping through the cells in column D like this:

CellCount = Range("D11").End(xlDown).Row
 
Upvote 0
Re:CellCount = Range("D11").End(xlDown).Row

That will work if the cells are truly blank (if you know what I mean) and not some sort of imported range or the results of formulae - correct Andrew?
 
Upvote 0
lasw10 said:
Re:CellCount = Range("D11").End(xlDown).Row

That will work if the cells are truly blank (if you know what I mean) and not some sort of imported range or the results of formulae - correct Andrew?

Yes - it will include null values that are the result of a formula. But I think the OP was just trying to finf the last used cell.
 
Upvote 0
Thanks,

adding this to my code did the trick:

CellCount = ActiveCell.Offset(-1,0).Row

But the other option you provide created an overflow error.

anyway it works so thanks alot
 
Upvote 0
Andy W said:
Thanks,

adding this to my code did the trick:

CellCount = ActiveCell.Offset(-1,0).Row

But the other option you provide created an overflow error.

anyway it works so thanks alot

The overflow error implies that you have declared CellCount as Integer and there is nothing below cell D11 - result 65536. Is that the case?
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top