Jumping to a column in current row in Excel

[Neib74656]

Hello Everyone,

I am currently writing a script that inputs data into an excel sheet. The script evaluates the data from the source and if data is present (not blank) from the source field inputs it into the correct cell in my sheet.

To achieve this I had to find the relative position of the column I want the source data to go into compared to the current column of my selected cell. Since the sequence of pasting data in my row varies depending on if the source fields have data in them or not. The current location of the selected sell has to be ascertained so that the script knows what action to take to get to the next desired location for data.

I have achieved this with the following as an example where I need data in the 22nd column and it works perfectly.

XLGetSelectedCell>SM,strValue,intRow,intCol
wait>WaitTime
let>I=22-intCol
setfocus>MISC Excel Workbook
press Right*I

I am hoping as a time saving measure that there is a way to immediately go to the desired column instead of pressing right or left the number of times needed to get there. I have tried various things with XLGetCell XLSetCell in an attempt to get my selection to jump to the correct column but I have not had any luck.

Any suggestions and help is greatly appreciated.

[Neib74656]

Please ignore the feature to input data by jumping to the wanted column is actually working using XLSetCell.

I was not paying enough attention and when I tested it on a blank workbook I realized it was working and that the UI showing the selected cell just does not change position to the cell where data was just added by Macro Scheduler.

[Neib74656]

Just to round this out for anyone interested in the fix. Using the native functions was the best solution as originally attempted. The issue I was having was that I though I had to be selected on the cell (border box around the cell) for the intended action to work. With the native MS excel functions the selected cell is actually irrelevant for my use case.

Code: Select all

Repeat>E
Let>E=E+1
XLGetCell>DI,Sheet1,E,1,D
setfocus>B
Press LCTRL
wait>WaitTime
Send>f
wait>WaitTime
Release LCTRL
wait>WaitTime
send D
wait>WaitTime
Press Enter
Wait>.2
IfWindowOpen>Microsoft Excel
press Enter
wait>WaitTime
press ESC
wait>WaitTime
XLSetCellColor>DI,Sheet1,E,1,R
goto>Continue1
endif
Press LALT
wait>WaitTime
Press F4
wait>WaitTime
Release LALT
WaitWindowClosed>Find and Replace
wait>WaitTime
XLGetCell>DI,Sheet1,E,5,D
XLGetSelectedCell>SM,,intRow,intCol
XLSetCell>SM,Sheet1,intRow,19,D,
XLSetCellColor>SM,Sheet1,introw,19,Y
XLGetCell>DI,Sheet1,E,6,D
XLSetCell>SM,Sheet1,intRow,6,D,
XLSetCellColor>SM,Sheet1,introw,6,Y
XLGetCell>DI,Sheet1,E,8,D
if>D>0
xlsetcell>SM,Sheet1,intRow,21,D,
XLSetCellColor>SM,Sheet1,introw,21,Y
endif
XLGetCell>DI,Sheet1,E,11,D
if>D>0
xlsetcell>SM,Sheet1,intRow,7,D,
XLSetCellColor>SM,Sheet1,introw,7,Y
endif
XLGetCell>DI,Sheet1,E,12,D
if>D>0
xlsetcell>SM,Sheet1,intRow,8,D,
XLSetCellColor>SM,Sheet1,introw,8,Y
endif
XLGetCell>DI,Sheet1,E,13,D
if>D>0
xlsetcell>SM,Sheet1,intRow,22,D,
XLSetCellColor>SM,Sheet1,introw,22,Y
endif
label>Continue1
Until>E=K

[Dorian (MJT support)]

Glad all is well. Its amazing how many people try to automate Excel by moving from cell to cell, and even copying and pasting or sending text. Macro Scheduler is so much more powerful than that.

[Neib74656]

Could not agree more Dorian!

The native Excel features are a god send. I have now rewritten many scrips and shaved a lot more time off of them. Also no more pesky "there was a problem with the clipboard errors" that seem to plague Excel and Microsoft has been playing wack-a-mole with for apparently years.