Hello, i need some help in Autohotkey for Excel automation
I want to look for empty cell in column D ( from the TOP first to DOWN) and select it. If the cell is not empty then it goes down and look for the empty cell, after found it, Select that empty cell.
After the empty cell is selected, set the value of the empty cell, and also some cells beside the selected empty cells ( the other empty cells beside the Selected empty cells, in that row )
For Example : Looking for the empty cell, If the empty cell found is cell D5, then select and set/change the value of C5, D5, and E5 to a variable.
Can somebody help me with this? i still can't figure how to write it in line of Autohotkey Code. Thankyou very much.
Want to Look for Empty Cells in Excel and Select it, then Set Value on that Cell and Some Cells beside it in that Row.
-
- Posts: 5
- Joined: 16 Sep 2019, 08:44
Re: Want to Look for Empty Cells in Excel and Select it, then Set Value on that Cell and Some Cells beside it in that Ro
This will help you get started.
If you need more help with COM and working with excel. Mickers has a good online tutorial that helped me with COM.
https://autohotkey.com/board/topic/69033-basic-ahk-l-com-tutorial-for-excel/
If you need more help with COM and working with excel. Mickers has a good online tutorial that helped me with COM.
https://autohotkey.com/board/topic/69033-basic-ahk-l-com-tutorial-for-excel/
Code: Select all
Workbook_Name := A_Desktop . "\This Workbook.xlsx"
xl := ComObjCreate("Excel.Application")
xl.Visible := true
xl.Workbooks.Open(Workbook_Name)
;~ This is to get the row count for your excel file
;~ Or you can do this Floor(xl.WorksheetFunction.CountA(xl.Sheets(1).Range("A:A")))
Row_Count := xl.Sheets(1).SpecialCells(11).Row ; Gets the last row in Sheet Tab 1
Loop, % Row_Count
{
Col_D := xl.Sheets(1).Range("D" . A_Index).Value
if (Col_D = "")
{
Some_variable := "Next Col has some value"
xl.Sheets(1).Range("D" . A_Index).Value := "Some Value Here"
xl.Sheets(1).Range("C" . A_Index).Value := Some_variable
xl.Sheets(1).Range("E" . A_Index).Value := Some_variable
}
}
xl.DisplayAlerts := 0
xl.ActiveWorkbook.Save
xl.Quit()
xl := ""
- flyingDman
- Posts: 2848
- Joined: 29 Sep 2013, 19:01
Re: Want to Look for Empty Cells in Excel and Select it, then Set Value on that Cell and Some Cells beside it in that Ro
try:
Code: Select all
xl := ComObjActive("excel.application")
frstblnk := xl.activesheet.range("D1").End(-4121).offset(1,0).row
xl.range("D" frstblnk).value := "b"
xl.range("D" frstblnk).offset(0,1).value := "c"
xl.range("D" frstblnk).offset(0,-1).value := "a"
14.3 & 1.3.7
Re: Want to Look for Empty Cells in Excel and Select it, then Set Value on that Cell and Some Cells beside it in that Ro
Thankyou Very Much for your Help FlyingDman, I really appreciate it ![Bravo! :bravo:](./images/smilies/bravo.gif)
![Bravo! :bravo:](./images/smilies/bravo.gif)
Re: Want to Look for Empty Cells in Excel and Select it, then Set Value on that Cell and Some Cells beside it in that Ro
Thankyou very much for your help TheUnknown 138, i really appreciate it ![Thumbup :thumbup:](./images/smilies/icon_thumbup.gif)
![Thumbup :thumbup:](./images/smilies/icon_thumbup.gif)