I am looking to write a macro to scrutinize an excel document composed of two columns: one column with heights and the second with a reference code assigned to each height (a file is attached as an example).
The user registers a height value in a UF and I would like the macro to look at the Excel file and show me in another box of the UF the reference assigned to this height. So far I can open excel but when I try to do the comparison operations: value entered in the UF by the user EQUALS the value of the column scrutinized, it doesn't work.
I checked and my program scrutinizes all the values (I displayed msgboxes for each loop tested: if, while, for, etc.) but the comparison doesn't work. As if he never found equal value.
I am on SW 2012
Private Sub MAJCmd_Click()
Dim xlApp As Excel.Application
Dim xlWB As Excel.Workbook
Dim exSheet As Excel.Worksheet
Dim i As Integer
'On ouvre Excel
Set xlApp = New Excel.Application
xlApp.Visible = True
'On ouvre le fichier de références des panneaux
Set xlWB = xlApp.Workbooks.Open("Y:\PDM\NAVIRES\TEST VALIDATION\CAO\TEST-Standards\Hauteurs Panneaux.xlsx")
Set exSheet = xlWB.ActiveSheet
xlApp.Visible = True
For i = 1 To 50 Step 1
If exSheet.Cells(i, 2).Value <> oCabinHigh Then
Else
oCodeHigh = Val(exSheet.Cells(i, 3))
End If
Next
MsgBox oCodeHigh
xlWB.Close
xlApp.Quit
Set exSheet = Nothing
Set xlWB = Nothing
Set xlApp = Nothing
End Sub
Given the code you display, it's Vba excel and I know a thing or two about vba SW but it must be similar. If I understand correctly what you WANT to write is that it searches in column A and in row i, i being the variable of your loop to make all the rows?
But for me you WRITE that you want to look in column i so 1=A, 2 =B etc in row 2 so you are not moving in the right direction. Review your conditions because to scrutinize all the lines you have to start from 2 and review the order in your cell designation because a Cell is a letter and a number.
In addition, I don't know in vba excel but on solidworks you have to convert the number into characters to get the letter of the column.
When the value is found, you have to get out of the loop, otherwise the code continues to execute and overwrites the value found. Wholesale:
For i = 1 To 50
If exSheet.Cells(i, 2).Value = oCabinHigh Then
oCodeHigh = Val(exSheet.Cells(i, 3))
Exit For
End If
Next
And for the cells and column in excel it starts from 1. Basically Column A row 1 = Cells(1,1) in this case in your code you scrutinize B and return C which is an empty value.
At the time for me, I made a copy/paste of my code, on my original excel I am on column B in reading and I get a column value C, I know that for that I have no problem. as I said I had tested the loops by making MsgBoxes appear to validate that I could read the data from the spreadsheet.
So I did write a VBA Code under SW to read Excel, I modified my For loop as indicated by @Cyril.f and I ask to look for the value 2110 (which exists in the first 50 values with the reference 0) but the match is not recognized.
For i = 3 To 20 Step 1
MsgBox exSheet.Cells(i, 2).Value & "....." & exSheet.Cells(i, 3).Value
If exSheet.Cells(i, 2).Value = oCabinHigh.Value Then
oCodeHigh = Val(exSheet.Cells(i, 3))
Exit For
End If
Next
Maybe I have an error in declaring variables that would cause a problem? or the Val(...) formula does not work or ...?
The loop scans all the cells from B3 to B20 (tested by the msgbox display) but that's it, I don't have anything more.