Finding a Blank Cell

[Bluemoon]

How do you right a script in AA to open a spread sheet and go to the first cell in a row taht has no data in it ?

[forumstaff]

Hello Bluemoon,

The following is the text of the task that we created according to your requirement:

1 Excel : Open Spreadsheet "C:\SampleExcel_SampleTask1.xlsx". Active Sheet: Default. Session: Default
2 Excel : Go to cell "A2". Session: Default
3 Excel : Get value of Active Cell and assign to variable "Prompt-Assignment". Session: Default
4 Loop While $Prompt-Assignment$ Not Equal To ""
5 If $Prompt-Assignment$ Includes "" Then
6 "Your statement"
7 End If
8 Excel : Go to one cell below the active cell . Session: Default
9 Excel : Get value of Active Cell and assign to variable "Prompt-Assignment". Session: Default
10 End Loop
11 Excel : Close Spreadsheet . Session: Default

Let us know if you have any doubts.

Thanks & Regards

[Bluemoon]

Sorry I can't get it work it work line 6 "Your statmate" what should I put in there becaus the scipt just keeps going from one cell to another with out stoping! How do I put a comand in this staement to get the loop to stop ?

[Bluemoon]

Even if I put Exit loop at line it still keeps gouing through the loop as if the if statement doest not work

[Bluemoon]

Please check my script as it will not work


1 Excel : Open Spreadsheet "H:\w81.csv". Active Sheet: Default. Session: CopyCells
2 Excel : Go to cell "A6". Session: CopyCells
3Excel : Get value of Active Cell and assign tovariable "PromptAssignment". Session: CopyCells
4 Loop While $Prompt-Assignment$ Not Equal To ""
5 If $Prompt-Assignment$ Includes "" Then
6 Excel : Go to cell "B6". Session: CopyCells
7 Exit Loop
8 End If
9 Excel : Go to one cell right of active cell . Session: CopyCells
10 Excel : Get value of Active Cell and assign to variable "Prompt-Assignment". Session: CopyCells
11 End Loop

[forumstaff]

Hello Bluemoon,

We saw the script and it looks fine. If you can send us the task file with the csv file that it is using, we will check it out at our end by running the script and if it doesn't work, we will edit it and will send it to you.

Thanks & Regards

[Bluemoon]

I got it work thanks but instead of 'includes' I used 'equal to'

Thanks

[trentonponder]

I would not recommend using the suggested method here by forumstaff if your spreadsheet has many rows. It can take quite awhile to loop through thousands of Excel rows, grab the value to a variable, analyse it and proceed.

I have a much better suggestion. Record an Excel Macro on the spreadsheet that navigates automatically to the last blank cell. When recording the macro, use F5 in Excel to open up the GoTo box, then select `special ` where you will see an option for `last cell.`

In AA call the Excel macro you recorded. This will instantly navigate to the last used cell.