Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Excel Single Row Checking
#1
Greetings,

I have a QM that obtains various inputs into variables from a CRM record.
The number of variables is different for each record.

Now, I need to check these variables against an excel sheet with corresponding data given.
If activecell is already selected for a particular record corresponding to the CRM one...
How do I go about checkin these variables against that excel row.

The data could be in any order throughout the row.

A find and colour if found routine is what I am looking at via QM

Appreciate your help.

Best Regards,
Philip
Best Regards,
Philip
#2
Macro Macro2488
Code:
Copy      Help
;gets all cells in the same row as the selected cell
ExcelSheet x.Init
int row; x.GetRangeInfo("<sel>" 0 row)
ARRAY(str) a; x.CellsToArray(a ExcelRow(row))
int i
for i 0 a.len(1)
,out a[i 0]
Macro Macro2489
Code:
Copy      Help
;finds text in cells in the same row as the selected cell
str textToFind="e"
ExcelSheet x.Init
int row; x.GetRangeInfo("<sel>" 0 row)
ARRAY(Excel.Range) a
if x.Find(textToFind a 2|4 ExcelRow(row))
,int i
,for i 0 a.len
,,Excel.Range& r=a[i]
,,;out r.Address(@ @ 2)
,,out F"col={r.Column} row={r.Row}"
Macro Macro2490
Code:
Copy      Help
;finds and highlights text in cells in the same row as the selected cell
str textToFind="e"
ExcelSheet x.Init
int row; x.GetRangeInfo("<sel>" 0 row)
ARRAY(Excel.Range) a
if x.Find(textToFind a 2|4 ExcelRow(row))
,int i
,for i 0 a.len
,,Excel.Range& r=a[i]
,,out F"col={r.Column} row={r.Row}"
,,;color
,,EXCELFORMAT f.cellColor=0xe0ff
,,x.Format(ExcelRange(r.Column r.Row) f)
,x.Activate(4)
#3
Thanks, I am in the process of adapting your code to my recuirement...
I need to change cell colour to a different one...
How do I know which code to use for what colour?
Best Regards,
Philip
#4
Can create from red green blue, 0-255.

int color=ColorFromRGB(255 128 0)

Read QM Help topic named "Color".
#5
Thanks Got it.

How do I refer to the first column in the same row in below code:

Macro Amended Orders AL - VISTA Verification
Code:
Copy      Help
,y.Format(ExcelRange(1 r2.Row) f)
Best Regards,
Philip
#6
This code refers to column A.
#7
I am getting the below mentioned error code when

Error (RT) in <open ":2991: /4839">Amended Orders AL - VISTA Verification: 0x80020009, Exception occurred.
0x3EC,. <help #IDP_ERR>?


Macro Amended Orders AL - VISTA Verification
Code:
Copy      Help
,es.Activate(2|4 "" "L405 Option Bank.xlsx")
,
,ExcelSheet x.Init(var1 1)
,ARRAY(Excel.Range) c
,Excel.Range r1
,
,;;*******FIND MODEL********
,if x.Find(varModel c 2)
,,r1=c[0].Offset(0 -1)
,,varModelV=r1.Value
,,out F"col={r1.Column} row={r1.Row} value={varModelV}"
,else
,,mes "Model Not Found"
,,goto errorHere
,
,;;*******FIND EXTERIOR COLOUR********
,x.Init("Color" 1)
,if x.Find(varSplitColour[0] c 2)
,,out F"Exterior Colour={varSplitColour[0]}"
,,r1=c[0].Offset(0 -1) ;;*****ISSUE HERE*****
,,varEColourV=r1.Value
,,out F"col={r1.Column} row={r1.Row} value={varEColourV}"
,else
,,mes "Exterior Colour Not Found"
,,goto errorHere
,
,;;*******FIND TRIM*********
,x.Init("Trim" 1)
,if x.Find(varSplitColour[1] c 2)
,,out F"Exterior Colour={varSplitColour[1]}"
,,r1=c[0].Offset(0 -1)
,,varIColourV=r1.Value
,,out F"col={r1.Column} row={r1.Row} value={varIColourV}"
,else
,,varIColourV=varSplitColour[1]
Best Regards,
Philip
#8
Probably c[0] is column A. Then -1 is no column.
#9
Yes, that was the issue; the same colour was repeating in both columns

So, how do I use 'find' method only in column B?
Best Regards,
Philip
#10
if c[0].Column>1

Or use range in Find. It is an optional parameter.
if x.Find(varModel c 2 "B:B")
#11
I mean, I want the searching to be done in column B only
On the code line below

Macro Amended Orders AL - VISTA Verification
Code:
Copy      Help
,if x.Find(varSplitColour[0] c 2)
Best Regards,
Philip
#12
if x.Find(varSplitColour[0] c 2 "B:B")
#13
Thanks,

And how do I change the 'activecell' colour?
Best Regards,
Philip
#14
I am getting the following error in the below shown code:

Error (RT) in <open ":2991: /6084">Amended Orders AL - VISTA Verification: Exception 0xC0000005. Access violation. Cannot read memory at 0x0. In qm.exe at 0x48C163 (0x400000+0x8C163)

Macro Amended Orders AL - VISTA Verification
Code:
Copy      Help
,;;*******FIND MODEL********
,if y.Find(varModelV b 2|4 ExcelRow(row1))
,,;for j 0 b.len
,,,r2=b[0] ;;***ISSUE HERE****
,,,out F"col={r2.Column} row={r2.Row}"
,,,;color
,,,excelColor=ColorFromRGB(0 255 0) ;;GREEN
,,,f.cellColor=excelColor
,,,y.Format(ExcelRange(r2.Column r2.Row) f)
,,,y.Activate(4)
,else
,,mes F"{varModelV} not found"
,,goto errorHere
Best Regards,
Philip
#15
No error here.
Macro Macro2496
Code:
Copy      Help
ExcelSheet y.Init
ARRAY(Excel.Range) b
if y.Find("f" b 2|4 "2:2")
,Excel.Range r2=b[0]
,out F"col={r2.Column} row={r2.Row}"

Maybe some other part of the macro corrupts memory somewhere.
Try to restart QM.
#16
I did restart QM and the PC itself.
But the error repeats.

Would you please be kind enough to have a look at the entire code for once and let me know where this error might be...

Macro Amended Orders AL - VISTA Verification
Code:
Copy      Help
;---- Recorded 21-Sep-13 7:16:17 AM ----
opt slowkeys 0; opt slowmouse 0; spe 80

int w, w1, w2, w4, w5, w6, w8, w12, w18, i, row
int wMain1, wMain2, wMain7,wChild, excelColor
Acc a, a2, a3, a4, a7, a12
str enquiry, varModel, varColour
ARRAY(str) enq, varSplitColour
ExcelSheet es
Excel.Range& r2
EXCELFORMAT f
RECT r
WindowText varOptions, wt


int varRep ;;string variable. If need numeric, replace str to int or double.
inp- varRep "Kindly input number of times to repeat this procedure"

rep varRep
,w1=wait(0 WV win("Showroom Customer Enquiries -" "KCMLMasterForm_32"))
,act w1
,w=wait(0 WV win("Showroom Customer Enquiries " "KCMLMasterForm_32"))
,scan "AL Recall Enquiry.bmp" w 0 1|2|16
,lef  ;;Click Recall Enquiry
,wait 0.75
,w4=wait(0 WV win("Showroom Enquiries - Find Customer Enquiries " "KCMLMasterForm_32"))
,wait 0.75
,a.Find(w4 "RADIOBUTTON" "...with reference:" "class=KCMLButton_32[]id=26695" 0x1005 30)
,a.Mouse(1)
,
,w2=act(win("Microsoft Excel - Mar-15 " "XLMAIN"))
,
,
,;get selected cells in Excel
,es.Init
,es.CellsToArray(enq "sel")
,
,
,act w1
,enquiry=enq
,
,;Enter enquiry number into reference
,for row 0 enq.len
,,key (enq[0 row]);;type cell
,
,;Click Next
,w6=wait(0 WV win("Showroom Enquiries - Find Customer Enquiries" "KCMLMasterForm_32"))
,a2.Find(w6 "PUSHBUTTON" "Next" "class=ToolBar_Class[]id=66" 0x1005 30)
,a2.Mouse(1)
,
,;Find enquiry# in record grid
,;idw
,wMain1=wait(0 WV win("Showroom Enquiries - Enquiry Search Results " "KCMLMasterForm_32"))
,wait 0.75
,w=id(100 wMain1) ;;KCMLGridPad_32
,SetRect &r 4 23 66 45
,wt.Init(w r)
,wt.Mouse(1 wt.Find(enquiry 0x1))
,err+
,,goto idw
,
,;Click Next
,wait 0.5
,a3.Find(wMain1 "PUSHBUTTON" "Next" "class=ToolBar_Class[]id=66" 0x1005 30)
,a3.Mouse(1)
,
,;Click Next
,wait 0.5
,;wait 30 WT w1 "Showroom Enquiries - Recalling A Quotation - Al Tayer - JLR (pjacob/161)"
,w8=wait(0 WV win("Showroom Enquiries - Recal" "KCMLMasterForm_32"))
,a4.Find(w8 "PUSHBUTTON" "Next" "class=ToolBar_Class[]id=66" 0x1005 30)
,a4.Mouse(1)
,
,;
,;Click 'Vehicle Details Section
,w5=wait(0 WV win("Showroom Enquiries - " "KCMLMasterForm_32"))
,;intw5
,wait 1
,scan "Vehicle Details.bmp" child("" "KCMLGridPad_32" w5 0x0 "id=100" 3) 0 1|2|16 8
,lef; 0.75
,err+
,,goto intw5
,;Get Vehicle Model
,wMain2=wait(0 WV win("Showroom Enquiries - Confirm Vehicle Selection " "KCMLMasterForm_32"))
,w=child("" "KCMLEdit32" wMain2 0x0 "" 5) ;;editable text
,wt.Init(w)
,varModel=wt.CaptureToString
,
,
,;Get Vehicle Colour
,w=child("" "KCMLEdit32" wMain2 0x0 "" 6) ;;editable text
,wt.Init(w)
,varColour=wt.CaptureToString
,wt.End
,;split
,tok(varColour varSplitColour 2 "/" 2)
,
,
,;Obtaining the Options **********************************************************************************************
,wMain7=wait(0 WV win("Showroom Enquiries - Confirm Vehicle Selection " "KCMLMasterForm_32"))
,wChild=id(100 wMain7) ;;KCMLGridPad_32
,varOptions.Init(wChild)
,varOptions.Capture
,varOptions.End
,
,;Click Next
,w12=wait(0 WV win("Showroom Enquiries - Confirm Vehicle Selection" "KCMLMasterForm_32"))
,a7.Find(w12 "PUSHBUTTON" "Next" "class=ToolBar_Class[]id=66" 0x1005 30)
,a7.Mouse(1)
,
,
,;Click Close
,w18=wait(0 WV win("Showroom Enquiries - " "KCMLMasterForm_32"))
,wait 0.5
,a12.Find(w18 "PUSHBUTTON" "Close" "class=ToolBar_Class[]id=66" 0x1005 30)
,a12.Mouse(1)
,
,wait 0.5
,
,
,;************************CHECK VARIABLE CONTENTS AGAINST EXCEL*****************************
,
,
,;;search for VISTA wordings from L405 Options Bank Excel
,
,str var1 varModelV varEColourV varIColourV
,
,if varModel = "Range Rover 5.0 HSE LE"
,,var1="L405HSE"
,else if varModel = "Range Rover 5.0 HSE"
,,var1="L405HSE"
,else if varModel = "Range Rover 5.0 SC Vogue LE"
,,var1="L405SCSE"
,else if varModel = "Range Rover 5.0 SC Vogue SE"
,,var1="L405SCSE"
,else if varModel = "Range Rover 5.0 SC Autobiography"
,,var1="L405BIO"
,
,es.Activate(2|4 "" "L405 Option Bank.xlsx")
,
,ExcelSheet x.Init(var1 1)
,ARRAY(Excel.Range) c
,Excel.Range r1
,
,;;*******FIND MODEL********
,if x.Find(varModel c 2 "B:B")
,,r1=c[0].Offset(0 -1)
,,varModelV=r1.Value
,,out F"col={r1.Column} row={r1.Row} value={varModelV}"
,else
,,mes "Model Not Found"
,,goto errorHere
,
,;;*******FIND EXTERIOR COLOUR********
,x.Init("Color" 1)
,if x.Find(varSplitColour[0] c 2 "B:B")
,,out F"Exterior Colour={varSplitColour[0]}"
,,r1=c[0].Offset(0 -1) ;;*****ISSUE HERE*****
,,varEColourV=r1.Value
,,out F"col={r1.Column} row={r1.Row} value={varEColourV}"
,else
,,mes "Exterior Colour Not Found"
,,goto errorHere
,
,;;*******FIND TRIM*********
,x.Init("Trim" 1)
,if x.Find(varSplitColour[1] c 2 "B:B")
,,out F"Exterior Colour={varSplitColour[1]}"
,,r1=c[0].Offset(0 -1)
,,varIColourV=r1.Value
,,out F"col={r1.Column} row={r1.Row} value={varIColourV}"
,else
,,varIColourV=varSplitColour[1]
,,
,;;*******FIND OPTIONS*********
,x.Init(var1 1)
,for i 0 varOptions.n
,,str tempOpt=varOptions.a[i].txt
,,if tempOpt="Ebony Macassar"
,,,continue ;;Skip and goto next for
,,if x.Find(tempOpt c 2)
,,,r1=c[0].Offset(0 -1)
,,,varOptions.a[i].txt=r1.Value
,,,out F"col={r1.Column} row={r1.Row} value={varOptions.a[i].txt}"
,,else
,,,out F"{varOptions.a[i].txt} Not Found"
,,,goto errorHere
,
,,
,;;finds and highlights text in cells in the same row as the selected cell
,;;On 'Mar-15' Excel with VISTA Data
,es.Activate(2|4 "" "Mar-15 Production 405.xlsx"); 0.5
,
,ExcelSheet y.Init
,int row1; y.GetRangeInfo("<sel>" 0 row1)
,ARRAY(Excel.Range) b
,
,;;*******FIND & HIGHLGHT MODEL********
,if y.Find(varModelV b 2|4 ExcelRow(row1))
,,r2=b[0] ;;***ISSUE HERE****
,,out F"col={r2.Column} row={r2.Row}"
,,;color
,,excelColor=ColorFromRGB(0 255 0) ;;GREEN
,,f.cellColor=excelColor
,,y.Format(ExcelRange(r2.Column r2.Row) f)
,,y.Activate(4)
,else
,,mes F"{varModelV} not found"
,,goto errorHere
,
,;;*******FIND & HIGHLGHT EXTERIOR COLOUR********
,if y.Find(varEColourV b 2|4 ExcelRow(row1))
,,r2=b[0]
,,out F"col={r2.Column} row={r2.Row}"
,,;color
,,excelColor=ColorFromRGB(0 255 0) ;;GREEN
,,f.cellColor=excelColor
,,y.Format(ExcelRange(r2.Column r2.Row) f)
,,y.Activate(4)
,else
,,mes F"{varEColourV} not found"
,,goto errorHere
,
,;;*******FIND & HIGHLGHT TRIM*********
,if y.Find(varIColourV b 2|4 ExcelRow(row1))
,,r2=b[0]
,,out F"col={r2.Column} row={r2.Row}"
,,;color
,,excelColor=ColorFromRGB(0 255 0) ;;GREEN
,,f.cellColor=excelColor
,,y.Format(ExcelRange(r2.Column r2.Row) f)
,,y.Activate(4)
,else
,,mes F"{varIColourV} not found"
,,goto errorHere
,,
,;;*******FIND & HIGHLGHT OPTIONS*********
,for i 0 varOptions.n
,,if varOptions.a[i].txt ="Ebony Macassar"
,,,if y.Find("Grand Black Wood (12)" b 2|4 ExcelRow(row1))
,,,,r2=b[0]
,,,,out F"col={r2.Column} row={r2.Row}"
,,,,;color
,,,,excelColor=ColorFromRGB(255 0 0) ;;RED
,,,,f.cellColor=excelColor
,,,,y.Format(ExcelRange(r2.Column r2.Row) f)
,,,,y.Activate(4)
,,,,continue ;;Skip the rest of the code and goto next for
,,,else if y.Find("Shadow Walnut Veneer" b 2|4 ExcelRow(row1))
,,,,r2=b[0]
,,,,out F"col={r2.Column} row={r2.Row}"
,,,,;color
,,,,excelColor=ColorFromRGB(255 0 0) ;;RED
,,,,f.cellColor=excelColor
,,,,y.Format(ExcelRange(r2.Column r2.Row) f)
,,,,y.Activate(4)
,,,,continue ;;Skip the rest of the code and goto next for
,,,
,,if y.Find(varOptions.a[i].txt b 2|4 ExcelRow(row1))
,,,r2=b[0]
,,,out F"col={r2.Column} row={r2.Row}"
,,,;color
,,,excelColor=ColorFromRGB(0 255 0) ;;GREEN
,,,f.cellColor=excelColor
,,,y.Format(ExcelRange(r2.Column r2.Row) f)
,,,y.Activate(4)
,,else
,,,'CR
,,,'R
,,,key (varOptions.a[i].txt)
,,,
,,
,es.Activate(2|4 "" "Mar-15 Production 405.xlsx")
,
,excelColor=ColorFromRGB(255 255 102) ;;YELLOW
,f.cellColor=excelColor
,y.Format(ExcelRange(2 r2.Row) f)
,y.Activate(4)
,
,goto saveHere
,
,;errorHere
,
,es.Activate(2|4 "" "Mar-15 Production 405.xlsx")
,
,excelColor=ColorFromRGB(255 0 0) ;;RED
,f.cellColor=excelColor
,y.Format(ExcelRange(2 r2.Row) f)
,y.Activate(4)
,
,;AllowActivateWindows
,
,;saveHere
,'D
,es.Save             ;; Ctrl+S
,
,
,wait 0.5

opt slowkeys 0; opt slowmouse 0; spe -1
;---------------------------------------
Best Regards,
Philip
#17
replace
Excel.Range& r2
to
Excel.Range r2

OR
everywhere replace
r2=b[0]
to
&r2=b[0]
#18
Did that and it works, Thanks.

Could you please explain the below code

Macro Amended Orders AL - VISTA Verification
Code:
Copy      Help
,y.GetRangeInfo("<sel>" 0 row1)

Does the above variable have the entire used row stored in it?
Can we go to the next cell beyond the last used one?
If so, what is the code to offset to it.
Basically, I need to enter unfound text in the last unused cell of the same row and highlight it in red.

Thanks,
Best Regards,
Philip
#19
Please read ExcelSheet.GetRangeInfo help, it will give most answers. Click GetRangeInfo in code and press F1.

Macro Macro2494
Code:
Copy      Help
ExcelSheet y.Init
int usedColumn1 usedColumnCount
y.GetRangeInfo("<used>" usedColumn1 0 usedColumnCount)
out F"{usedColumn1} {usedColumnCount}"
;select cell in the first unused column in the selected row
int row1
y.GetRangeInfo("<sel>" 0 row1)
y.SelectCell(usedColumn1+usedColumnCount row1)
act "Excel"


Forum Jump:


Users browsing this thread: 1 Guest(s)