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

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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?
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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.
 

Andy W

Board Regular
Joined
Jul 31, 2003
Messages
68

ADVERTISEMENT

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
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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?
 

Andy W

Board Regular
Joined
Jul 31, 2003
Messages
68
Thanks for that, as you can see I am still learning.

Cheers

Andy
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,563
Messages
5,765,123
Members
425,262
Latest member
sabry

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
Top