Next Blank Cell in uncontinuous Range

[robfo0]

Hi everyone,

I have a macro, and im trying to find the next blank cell in a row (to the right of C3), BUT the problem is sometimes C1 is blank, sometimes C2 is blank, and sometimes both are blank, but regardless i want to find the the next blank cell to the right of C3. I have this code (which finds the first blank cell, even if it is C1, or C2):

Workbooks("Sheet").Sheets("Contractor Data").Range("C3").End(xlToLeft).Offset(0,1) = "Test"

How can i do this?

Thanks
This message was edited by robfo0 on 2002-09-05 16:23

 

[larai]

Try:

ActiveSheet.Range(Cells(3, 3).End(xlToRight), Cells(3, 3).End(xlToRight)).Offset(0, 1) = "Test"

 

[robfo0]

I just tried that, but when it runs, i get a run time 1004 error: application-defined or object defined error. This is the exact line i have used:

Workbooks("aa Test.xls").Sheets("Contractor Data").Range(Cells(3, 3).End(xlToRight), Cells(3, 3).End(xlToRight)).Offset(0, 1) = "note"

any ideas? thanks

 

[robfo0]

Well, i figured out a way to do it, but i dont think its the most efficient. Using a counter i cant increase the offset:

TempCount=1
Do Until loop here...

Workbooks("aa Test.xls).Sheets("Contractor Data").Range("C3").Offset(0, TempCount) = "Note"
TempCount = TempCount + 1
Loop

I would appreciate it if anyone has a better solution. Thanks

 

[larai]

If you want to continue this thread with me, send me an email response. For some reason it's taking a long time to get through the posts.

You can email me @ larai@saturnee.com

Are you in this workbook when you are running the macro or are you running the code from a different workbook?

 

[manet]

[D3:IV3].SpecialCells(xlCellTypeBlanks)(1, 1) = "Test"