AutoHotkey Community

Let's help each other out
https://www.autohotkey.com:443/boards/

Paste variables in a repeated cell pattern in Excel

https://www.autohotkey.com:443/boards/viewtopic.php?f=76&t=63160

Page 1 of 1

Paste variables in a repeated cell pattern in Excel

Posted: 28 Mar 2019, 05:04
by AviationGuy
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'...

Re: Paste variables in a repeated cell pattern in Excel

Posted: 28 Mar 2019, 06:50
by Odlanir
Some like this?

Code: Select all

Var:=1
Xl := ComObjActive("Excel.Application")
Xl.Range(xl.cells(1,1),xl.cells(5,5)).Value := Var

Re: Paste variables in a repeated cell pattern in Excel

Posted: 28 Mar 2019, 07:02
by AviationGuy
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
	}
}

Re: Paste variables in a repeated cell pattern in Excel

Posted: 28 Mar 2019, 07:41
by AviationGuy
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
	}
}

Re: Paste variables in a repeated cell pattern in Excel

Posted: 28 Mar 2019, 11:34
by sinkfaze

Code: Select all

xl :=	ComObjActive("Excel.Application")
xl.Range("A1:E3").Value :=	1
:?:

Re: Paste variables in a repeated cell pattern in Excel

Posted: 28 Mar 2019, 17:11
by AviationGuy
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 :)

Re: Paste variables in a repeated cell pattern in Excel

Posted: 28 Mar 2019, 17:20
by Blue Kodiak
Have you tried FillDown and FillRight?

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

Posted: 28 Mar 2019, 20:42
by Tigerlily
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
		
	}

Re: Paste variables in a repeated cell pattern in Excel

Posted: 29 Mar 2019, 03:42
by AviationGuy
@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!

Re: Paste variables in a repeated cell pattern in Excel

Posted: 29 Mar 2019, 08:44
by Tigerlily
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..

Re: Paste variables in a repeated cell pattern in Excel

Posted: 29 Mar 2019, 09:16
by AviationGuy
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.

Re: Paste variables in a repeated cell pattern in Excel

Posted: 29 Mar 2019, 10:31
by AviationGuy
Wow! It really is much faster when you use SetBatchLines, -1.
ExcelTemp.PNG
ExcelTemp.PNG (3.94 KiB) Viewed 596 times
It's 2 times faster, great!
All times are UTC-05:00
Page 1 of 1
Powered by phpBB® Forum Software © phpBB Limited
https://www.phpbb.com/