Paste variables in a repeated cell pattern in Excel Topic is solved

Get help with using AutoHotkey (v1.1 and older) and its commands and hotkeys
AviationGuy
Posts: 188
Joined: 17 Jan 2019, 10:13

Paste variables in a repeated cell pattern in Excel

28 Mar 2019, 05:04

Hi there!

I want to paste a variable multiple times in the first (3) rows of Excel. The vars are obtained by a function
It should look like this.
ExcelExamplePost.PNG
(3.34 KiB) Downloaded 73 times
The code I'm using to try to achieve this is the following but it isn't working correctly atm.

Code: Select all

Var:=1
Myfunc(2)
Myfunc(2)
Myfunc(1)

MyFunc(var){
	Trigger:=var
}

loop 5{
	Xl := ComObjActive("Excel.Application")
	Xl.Range("A1").Value := Var
	if (trigger!=1){
		Xl.ActiveCell.Offset(A_Index, A_Index=0).Value := Var
	} else {
		Xl.ActiveCell.Offset(A_Index-2, A_Index).Value := Var
	}
}
The thing I'm trying to do here is select cell 'A1' and paste the 'var'. This works but after this, it doesn't work anymore.
Then go to the cell below and paste the 'var' again. Repeat this one more time and paste the 'var' in cell 'A3'.
The next var should be placed in cell 'B1' again and the 2 'vars' after in cells 'B2' and 'B3'.
I added the 'Trigger' variable so the if function knows when to go to the next column. So after every 3rd 'var' placement, the column should go 1 to the right and the row should go 2 up (that is what I'm trying to do in the 'else' part of the if-statement).

Any help is appreciated!

***EDIT***
I think the Xl.Range("A1").Value := Var should be outside the loop bcs otherwise it would keep resetting the active cell to 'A1'...
Odlanir
Posts: 659
Joined: 20 Oct 2016, 08:20

Re: Paste variables in a repeated cell pattern in Excel

28 Mar 2019, 06:50

Some like this?

Code: Select all

Var:=1
Xl := ComObjActive("Excel.Application")
Xl.Range(xl.cells(1,1),xl.cells(5,5)).Value := Var
____________________________________________________________________________
Windows 10 Pro 64 bit - Autohotkey v1.1.30.01 64-bit Unicode
AviationGuy
Posts: 188
Joined: 17 Jan 2019, 10:13

Re: Paste variables in a repeated cell pattern in Excel

28 Mar 2019, 07:02

Hi thanks for the reply.
Unfortunately, I don't think this is an option because 'var' changes after it's placed in a cell. That's why I was using a loop.

In the meantime, I tried things like this but this also won't work...
'TimeMS' is the variable that changes after each time a function gets run.

Code: Select all

loop 3 {
	MyFunc(2)
	MyFunc(2)
	MyFunc(1)
}

MyFunc(var){
	TimeMS:=1
	Trigger:=var
	;~ msgbox % trigger
	Xl := ComObjActive("Excel.Application")
	Xl.Range("A1").Value := TimeMS
	if (Trigger!=1){
		Xl.ActiveCell.Offset(A_Index, 0).Select
		Xl.ActiveCell.Value := TimeMS
	} else {
		Xl.ActiveCell.Offset(A_Index-2, A_Index).Value := TimeMS
	}
}
AviationGuy
Posts: 188
Joined: 17 Jan 2019, 10:13

Re: Paste variables in a repeated cell pattern in Excel

28 Mar 2019, 07:41

Got this now but this only pastes a 1 in 'A2' and 'B1'...

Code: Select all

TimeMS:=1
Xl := ComObjActive("Excel.Application")
Xl.Range("A1").Select

loop 6 {
	MyFunc(2)
	MyFunc(2)
	MyFunc(1)
}

MyFunc(var){
	TimeMS:=1
	Trigger:=var
	if (Trigger!=1){
		Xl.ActiveCell.Offset(1, 0).Select
		Xl.ActiveCell.Value := TimeMS
	} else {
		x1.Selection.Resize(x1.Selection.Rows.Count - 2).Select
		Xl.ActiveCell.Offset(0, 1).Select
		Xl.ActiveCell.Value := TimeMS
	}
}
User avatar
sinkfaze
Posts: 616
Joined: 01 Oct 2013, 08:01

Re: Paste variables in a repeated cell pattern in Excel

28 Mar 2019, 11:34

Code: Select all

xl :=	ComObjActive("Excel.Application")
xl.Range("A1:E3").Value :=	1
:?:
AviationGuy
Posts: 188
Joined: 17 Jan 2019, 10:13

Re: Paste variables in a repeated cell pattern in Excel

28 Mar 2019, 17:11

Yeah but that is the same problem as odlanir right? The value isn’t constant. I just put 1 there in the example. Each cell is going to have a different value.

I had this script working with activate excel-click A1-ctrl v-arrow down-ctrl v-arrow down-ctrl v-arrow right-arrow up (2x), and loop this process. But this, ofcourse will only work when excel is active and i wanted the script to run in de background.

Thanks for your time anyways :)
User avatar
Blue Kodiak
Posts: 26
Joined: 17 Mar 2019, 00:45

Re: Paste variables in a repeated cell pattern in Excel

28 Mar 2019, 17:20

Have you tried FillDown and FillRight?
User avatar
Tigerlily
Posts: 377
Joined: 04 Oct 2018, 22:31

Re: Paste variables in a repeated cell pattern in Excel  Topic is solved

28 Mar 2019, 20:42

AviationGuy wrote:
28 Mar 2019, 17:11
Yeah but that is the same problem as odlanir right? The value isn’t constant. I just put 1 there in the example. Each cell is going to have a different value.

I had this script working with activate excel-click A1-ctrl v-arrow down-ctrl v-arrow down-ctrl v-arrow right-arrow up (2x), and loop this process. But this, ofcourse will only work when excel is active and i wanted the script to run in de background.

Thanks for your time anyways :)
try this:

Code: Select all

	TimeMS:=1
,	Xl := ComObjActive("Excel.Application")
,	Xl.Range("A1").Select

loop 2 
{
		MyFunc(2)
	,	MyFunc(2)
	,	MyFunc(1)		
}

MyFunc(var){
global

		Trigger:=var
	,	Xl.Selection.Value := TimeMS
	
		if trigger != 1
			Xl.Selection.Offset(1,0).Select
		else
			Xl.Selection.Offset(-2,1).Select
		
	}
-TL
AviationGuy
Posts: 188
Joined: 17 Jan 2019, 10:13

Re: Paste variables in a repeated cell pattern in Excel

29 Mar 2019, 03:42

@Blue Bear
No I haven't looked into those yet. I will do after I tried Tigerlily's solution!

@Tigerlily
Oh man, that's great! It's doing exactly what I want! :)
I implemented your code in my script and it works perfectly! If someone is interested I posted my script below. The script measures the time it takes to apply a value to a variable inside if-statements and outside.
I need to do this because I'm building a script with a lot of if's and need to test if all the if-statements have a big influence on the run time (conlusion=they don't).
Below I've made 3 'functions' that assign values to a variable with and without the help of the if-statements. It runs the 3 functions 1000 times (could be changed) to get a reliable outcome which I'm able to use to prove that if-statements have (not) an influence on script run time.
The runtime of each 'assigning-value-to-variable-function' gets pasted in Excel immediately.

Code: Select all

x = 1
z = 2
Xl := ComObjActive("Excel.Application")
Xl.Range("A1").Select
loop 1000 {
	Speed1()
	Trigger = 0
	Loop 1000000
	{
		y = a
	}
	Speed2Msgbox("Just assigning a value to a variable 1 million times took ")

	Speed1()
	Trigger = 0
	Loop 1000000
	{
		If (x = 1)
			y = a
	}
	Speed2Msgbox("Testing a variable against a value and then assigning a value, each 1 million times, took ")

	Speed1()
	Trigger = 1
	Loop 1000000
	{
		If (x = 1){
		}
		If (x = 2)
			if (x != 3)
				if(z = 2)
					y = a+=2
	}
	Speed2Msgbox("Testing against a value twice and then assigning a value, each 1 million times, took ")
}
Return

;----Functions to measure performance/speed----
Speed1() {
	global
	DllCall("QueryPerformanceCounter", "Int64*", CounterBefore)
}

Speed2() {
	global  
	DllCall("QueryPerformanceCounter", "Int64*", CounterAfter)
}

Speed2Msgbox(MessageBegin = "The operation took ") {
	global
	DllCall("QueryPerformanceCounter", "Int64*", CounterAfter)
	DllCall("QueryPerformanceFrequency", "Int64*", Frequency)
	TimeMS := (CounterAfter - CounterBefore) / (Frequency / 1000) 
	
	If WinActive("ahk_exe excel.exe") {
	; global							removed this because the variable is declared inside the function? got an error when I kept it in
	Xl.Selection.Value := TimeMS
		if (Trigger!=1){
			Xl.Selection.Offset(1,0).Select
		} else {
			Xl.Selection.Offset(-2,1).Select
		}
	} else {
		WinActivate, ahk_exe excel.exe
		Xl.Selection.Offset(1,0).Select
	}
}
return
The script runtime function is written by Cerberus and I modified it with your help so it pasts the runtime directly in Excel. I know nothing about DllCall so I couldn't change much about the setup. I'm glad it works now.

Only one thing tho, which isn't a problem really but just for the sake of it...
Cell 'A1' remains empty atm, which is obvious bcs cell 'A1' gets selected and Xl.Selection.Offset(1,0).Select this gets ran immediately after. As I already mentioned, it doesn't matter bcs the runtime gets taken 999 times instead of 1000 but is there a way to ensure that cell 'A1' gets also filled in?

Thanks again, love it!
User avatar
Tigerlily
Posts: 377
Joined: 04 Oct 2018, 22:31

Re: Paste variables in a repeated cell pattern in Excel

29 Mar 2019, 08:44

AviationGuy wrote:
29 Mar 2019, 03:42
@Tigerlily
Oh man, that's great! It's doing exactly what I want!
Do it faster!!:

Code: Select all

SetBatchLines, -1	;	<~~ don't test speed performance without this up top, will dramatiicaly improve speeds

	x = 1
,	z = 2
,	Xl := ComObjActive("Excel.Application")
,	Xl.Range("A1").Select
loop 1000 {
	Speed1()
	Trigger = 0
	Loop 1000000
	{
		y = a
	}
	Speed2Msgbox("Just assigning a value to a variable 1 million times took ")

	Speed1()
	Trigger = 0
	Loop 1000000
	{
		If (x = 1)
			y = a
	}
	Speed2Msgbox("Testing a variable against a value and then assigning a value, each 1 million times, took ")

	Speed1()
	Trigger = 1
	Loop 1000000
	{
		If (x = 1){
		}
		If (x = 2)
			if (x != 3)
				if(z = 2)
					y = a+=2
	}
	Speed2Msgbox("Testing against a value twice and then assigning a value, each 1 million times, took ")
}
Return

;----Functions to measure performance/speed----
Speed1() {
	global
	DllCall("QueryPerformanceCounter", "Int64*", CounterBefore)
}

Speed2() {
	global  
	DllCall("QueryPerformanceCounter", "Int64*", CounterAfter)
}

Speed2Msgbox(MessageBegin = "The operation took ") {
	global
	DllCall("QueryPerformanceCounter", "Int64*", CounterAfter)
	,	DllCall("QueryPerformanceFrequency", "Int64*", Frequency)
	,	TimeMS := (CounterAfter - CounterBefore) / (Frequency / 1000) 
	
	If WinActive("ahk_exe excel.exe") {
	; global							removed this because the variable is declared inside the function? got an error when I kept it in
	Xl.Selection.Value := TimeMS
		if (Trigger!=1){
			Xl.Selection.Offset(1,0).Select
		} else {
			Xl.Selection.Offset(-2,1).Select
		}
	} else {
		WinActivate, ahk_exe excel.exe
		Xl.Selection.Offset(1,0).Select
	}
}
return
AviationGuy wrote:
29 Mar 2019, 03:42
Only one thing tho, which isn't a problem really but just for the sake of it...
Cell 'A1' remains empty atm, which is obvious bcs cell 'A1' gets selected and Xl.Selection.Offset(1,0).Select this gets ran immediately after. As I already mentioned, it doesn't matter bcs the runtime gets taken 999 times instead of 1000 but is there a way to ensure that cell 'A1' gets also filled in?

Thanks again, love it!
IDK - worked fine before you added the rest of your code.. maybe add an OR statement tobefore the offset(1,0) that says A_Index > 1 ?? idk this co does not produce any output on my device..
-TL
AviationGuy
Posts: 188
Joined: 17 Jan 2019, 10:13

Re: Paste variables in a repeated cell pattern in Excel

29 Mar 2019, 09:16

Tigerlily wrote:
29 Mar 2019, 08:44
Do it faster!!:

Code: Select all

SetBatchLines, -1	;	<~~ don't test speed performance without this up top, will dramatiicaly improve speeds
Added it, thanks!
Tigerlily wrote:
29 Mar 2019, 08:44
AviationGuy wrote:
29 Mar 2019, 03:42
Only one thing tho, which isn't a problem really but just for the sake of it...
Cell 'A1' remains empty atm, which is obvious bcs cell 'A1' gets selected and Xl.Selection.Offset(1,0).Select this gets ran immediately after. As I already mentioned, it doesn't matter bcs the runtime gets taken 999 times instead of 1000 but is there a way to ensure that cell 'A1' gets also filled in?

Thanks again, love it!
IDK - worked fine before you added the rest of your code.. maybe add an OR statement tobefore the offset(1,0) that says A_Index > 1 ?? idk this co does not produce any output on my device..
Yeah that's because in your code the variable 'TimeMS' was given a value already (namely 1). See below.

Code: Select all

	TimeMS:=1
,	Xl := ComObjActive("Excel.Application")
,	Xl.Range("A1").Select

However, in my script that is removed now and the first time 'TimeMS' gets a value again it is pasted in cell 'A2'.
It doesn't produce an output? It should I think. Try opening an Excel file and run the code. Maybe add a hotkey to start the script, that seems to work for me.
AviationGuy
Posts: 188
Joined: 17 Jan 2019, 10:13

Re: Paste variables in a repeated cell pattern in Excel

29 Mar 2019, 10:31

Wow! It really is much faster when you use SetBatchLines, -1.
ExcelTemp.PNG
ExcelTemp.PNG (3.94 KiB) Viewed 2524 times
It's 2 times faster, great!

Return to “Ask for Help (v1)”

Who is online

Users browsing this forum: No registered users and 62 guests