Can I put an if statement in a macro

Marcia

New Member
Joined
Sep 24, 2002
Messages
4
Hello, I'm wondering if I could put an IF statement into a macro. I want to transport data to the last available cell in a row. I can record a macro to put it into a specific cell but the action of that being the last available cell is not recognized. The next time the macro is run the cell data from the previous macro run is overwritten as only the specific cell is recognized, as I said before, and not the action of taking it to the last available cell. If I could somehow tell Excel to put it into the cell if the cell has no visible data in it (zeros are blanked out) and if the cell is full to move to the right once, all my prayers would be answered!
Is this possible???
Thanks
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Hi Marcia,

You could use a line like this to select the next blank cell in row1 starting from A1.

Range("A1").End(xlToRight).Offset(0, 1).Select

Modify to suit your macro requirements.

HTH
 

Fryer Tuck

Board Regular
Joined
Mar 20, 2002
Messages
64
Marcia:
Ritchie is on the right track, but there are some cautions to his answer. Using the "End" statement will find the last cell used in a sequence. So if you have some blank cells mixed in with the used cells, his formula will find the first blank, not the last.

You could use
Range("a1").SpecialCells(xlCellTypeLastCell).offset(,1).Select
to get to the absolute last cell used on the sheet, then go to the right 1 cell.

What you may want to do is start at the far right, go to the left to find the last entry, then offset to the right 1 column.

Range("IV20").End(xlLeft).Offset(,1).Select

Good luck
Tom
 

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
Tom,

You are, of course, correct in what you say. I was just trying to follow the sentiment in Marcia's request, ie the next blank cell to the right :wink:.

Marcia,

I'd stick with Tom's suggestion of working from the left if you do have any intended blanks in the row. The special cells method is not very reliable.

A quote from Dave Hawley's site:

You can use Edit>Go to-Special-Last cell to try and find the last cell in the active sheet, but it is not very reliable. The reasons are two-fold:

1. The last cell is only re-set when you save. This means if you enter any number or text in say, cell A10 and A20 of a new Worksheet, then delete the content of A20, the Edit>Go to-Special-Last cell will keep taking you to A20, until you save.

2. It picks up cell fomatting. Let's say you enter any text or number in cell A10 and then enter a valid date in cell A20 of a new Worksheet. Now delete the date in cell A20 and save. The Edit>Go to-Special-Last cell will still take you to A20. This is because entering a date in A20 has caused Excel to automatically format the cell from "General" to a Date format. To stop from going to A20 you will have to use Edit>Clear>All and then save.

So when using VBA you cannot rely on:
Range("A1").SpecialCells(xlCellTypeLastCell).Select


HTH
 

Forum statistics

Threads
1,144,312
Messages
5,723,658
Members
422,509
Latest member
Njugunah

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