Excel paste into next column

[M4rk_My_W0rds]

Can someone please help me i'm trying to paste into next column every time I run my script. As is the code I have only lets me paste straight down in one column.
i'm really knew to AHK and I use bits and pieces of code that I find to get what I need done. I just don't understand the language involved to get the outcome that i seek. any help is greatly appreciated.

Code: Select all

NumpadAdd::
Gui, Destroy
Gui, Add, text, , Enter #:
Gui, Add, Edit, vNum
Gui, Add, Button, default, OK
Gui, Show
Return

GuiClose:
ButtonOK:

Gui, Submit,

Xl := ComObjCreate("Excel.Application") ;create handle
Xl.Workbooks.Open("C:\Users\Maherna\Desktop\CR.xlsx")
Xl.Visible := False

Loop
{
     if(Xl.Range("B" . A_Index).Value = "")
     {
           Xl.Range("B" . A_Index).Value:= Num
           break
     }
}

Xl.ActiveWorkbook.save()
Xl.ActiveWorkbook.close()
return

^!NumpadAdd::ExitApp

I would like to paste into the next (empty) column instead. Please someone help me!!

[AlphaBravo]

next empty column on row 1

Code: Select all

while xl.Cells(1, A_Index).value
{
	col := A_Index+1
	continue
}
xl.Cells(1, col) := num

[M4rk_My_W0rds]

Thanks for your reply and help, but I cant seem to get it to work and excel doc is staying open in that background, most likely because the script isn't finishing. I get an error at line 28.

Also is there a way to get it to finish at column V then drop to next row down and start at A. So as I input info into the gui box it will populate cells (A1 B1 C1 D1 ----> all the way to V1) one by one. then when at the end drop to next row (A2 B2 C2 D2 ----->ETC..) Rinse and repeat.

Here is my code

Code: Select all

^NumpadSub::
Gui, Destroy
Gui, Add, text, , Enter #:
Gui, Add, Edit, vNum
Gui, Add, Button, default, OK
Gui, Show
Return

GuiClose:
ButtonOK:

Gui, Submit,

Xl := ComObjCreate("Excel.Application")
Xl.Workbooks.Open("C:\Users\Maherna\Desktop\Closed.xlsx") 
Xl.Visible := False

while xl.Cells(1, A_Index).value
{
	col := A_Index+1
	continue
}
xl.Cells(1, col) := num


Xl.ActiveWorkbook.save()
Xl.ActiveWorkbook.close()
return


^!NumpadSub::ExitApp

Again thank you

[AlphaBravo]

not the best way to open and close excel sheet every time!

Code: Select all

ButtonOK:
Gui, Submit,

Xl := ComObjCreate("Excel.Application") ;create handle
Xl.Workbooks.Open("C:\Users\Maherna\Desktop\CR.xlsx")
Xl.Visible := False

row := xl.ActiveSheet.Range("A" xl.Rows.Count).End(xlUp:=-4162).Row
col := 1
while xl.Cells(row, col).value
{
	col++
	if col > 22					; col "V" 
		col := 1, row++
	continue
}
xl.Cells(row, col).value := num

Xl.ActiveWorkbook.save()
Xl.ActiveWorkbook.close()
return

[FanaticGuru]

next empty column on row 1

Code: Select all

while xl.Cells(1, A_Index).value
{
	col := A_Index+1
	continue
}
xl.Cells(1, col) := num

The Find method is normally quicker and easier than looping through all the cells.

Code: Select all

xlApp := ComObjActive("Excel.Application")
MsgBox % Empty_Column := xlApp.Rows(1).Find("*",,,,,2).Column + 1 ; empty column after last data in row 1
MsgBox % Empty_Row := xlApp.Columns(1).Find("*",,,,,2).Row + 1 ; empty row  after last data in column 1

FG

[M4rk_My_W0rds]

AHH thanks AlphaBravo that works great!! just what I needed.

@ FanaticGuru I don't quit understand your code/method how would that be implemented in my code to do the same task. to input data in cells up to column V/22 then start back at the next free row. I am interested in learning your method as well or should I say the find method. thank you so much as well for your time to post a message.

[AlphaBravo]

AHH thanks AlphaBravo that works great!! just what I needed.

@ FanaticGuru I don't quit understand your code/method how would that be implemented in my code to do the same task. to input data in cells up to column V/22 then start back at the next free row. I am interested in learning your method as well or should I say the find method. thank you so much as well for your time to post a message.

FG was just showing you another way of finding the last used row / column
for your application use it like so

Code: Select all

Empty_Row := xl.Columns(1).Find("*",,,,,2).Row 					; Last Row with data in column 1 (Search for anything "backward")
Empty_Column := xl.Rows(Empty_Row).Find("*",,,,,2).Column + 1 	; empty column after last data in "Last Row"
if (Empty_Column = 22)
	Empty_Row++, Empty_Column := 1

MsgBox % Empty_Row ", " Empty_Column

xl.Cells(Empty_Row, Empty_Column).value := num