Excel COM - Dynamic Variables within Range Topic is solved

[Tigerlily]

Hi Folks,

I'm simply trying to use "X" as a variable within Excel COM Ranges.

My code below uses "OCT"

wUSA := Xl.Worksheets("USA TRAFFIC REPORT")
OCT := Month+9
Month := 44
wUSA.Range("K" OCT).Value := "-"

I want to be able to specify a month within the range so that if I use JAN, the selected cell stays put in the same place, and if I use OCT, the selected cell moves down 9 rows before inputing the value. In this way, I select a month, and then the code specifies the appropriate row to input a value.

Here is a snippet of the code I'm trying to work through:

Code: Select all

#SingleInstance, force ;	Doesn't allow the script to run multiple instances at once.
#NoEnv ; Recommended for performance and compatibility with future AutoHotkey releases. 
#Warn ; Enable warnings to assist with detecting common errors. 
SendMode Input ; Recommended for new scripts due to its superior speed and reliability. 
SetWorkingDir %A_ScriptDir% ; Ensures a consistent starting directory. 

;~ Xl := ComObjCreate("Excel.Application") ;handle if Excel has an active window already
Xl := ComObjActive("Excel.Application") ;handle if Excel has an active window already
;~ Xl.Workbooks.Open("C:\Users\hcounts\OneDrive - Dentsu Aegis Network\Samsung\MONTHLY REPORTING\FINAL VERSIONS\FINAL FINAL\OCTOBER\Samsung SEO Monthly Report October 2018.xlsx", 0)
Xl.Visible := True

wES := Xl.Worksheets("EXECUTIVE SUMMARY")
wUSA := Xl.Worksheets("USA TRAFFIC REPORT")
wGOLD := Xl.Worksheets("USA GOLD LIST REPORT")
wKW := Xl.Worksheets("KW CATEGORIES")
wODATA := Xl.Worksheets("OMNITURE DATA")
wSDATA := Xl.Worksheets("STAT DATA")

; lets you copy data to the correct cell corresponding to the specified month of reporting
Month :=
JAN := Month+0
FEB := Month+1
MAR := Month+2
APR := Month+3
MAY := Month+4
JUN := Month+5
JUL := Month+6
AUG := Month+7
SEP := Month+8
OCT := Month+9
NOV := Month+10
DEC := Month+11

		;		//		YEAR-OVER-YEAR NATURAL SEARCH  TABLE		//		

Month := 44
wUSA.Range("E52:M52").AutoFill(wUSA.Range("E52:M53"))

	wUSA.Range("B44:M45").Copy
	wUSA.Range("B46:M55").PasteSpecial(-4122)

		wUSA.Range("K" OCT).Value := "-"
		wUSA.Range("K" OCT).Interior.ColorIndex := 6

			wUSA.Range("H" OCT).Value := "-"
			wUSA.Range("H" OCT).Interior.ColorIndex := 6

				wUSA.Range("EOCT").Value := wODATA.Range("B12").Value

Thanks so much in advance (:(: I looked around pretty hard and was suprised to not easily find this anywhere ):):

[TheDewd]

'OCT' is assigned a value of "" + 9.

You can't use the addition operator on an empty string and a number value.

Assigning the value '44' to the 'Month' variable does not change the previously assigned value for 'OCT'.

'Month' would need to be assigned a value BEFORE using it with the month calculations.

Try this...

Code: Select all

JAN := 0
FEB := 1
MAR := 2
APR := 3
MAY := 4
JUN := 5
JUL := 6
AUG := 7
SEP := 8
OCT := 9
NOV := 10
DEC := 11

Month := 44
wUSA.Range("E52:M52").AutoFill(wUSA.Range("E52:M53"))
	wUSA.Range("B44:M45").Copy
	wUSA.Range("B46:M55").PasteSpecial(-4122)
		wUSA.Range("K" OCT + Month).Value := "-"
		wUSA.Range("K" OCT + Month).Interior.ColorIndex := 6
			wUSA.Range("H" OCT + Month).Value := "-"
			wUSA.Range("H" OCT + Month).Interior.ColorIndex := 6
				wUSA.Range("EOCT").Value := wODATA.Range("B12").Value

[Tigerlily]

Thanks Dewd!

I really appreciate your insight & feedback. I didn't realize that I had set OCT as a permanently empty value, I thought that when I set Month := 44 that it would recalculate OCT automatically, but that makes sense why it doesn't.

I'm making a lot of progress because of your help. thank you thank you thank you (:(:(: