VBA Excel Help

[giankr]

Hello there,

I am trying to write some VBA code that will loop through the first column of a sheet until the value of the cell matches the value of a variable previously stored from an inputbox. Then return the value of the cell next to it, in a message box.
I just started using VBA so I am not sure how to debug correctly, or how to find my error. Help is appreciated. Thank you.

This is what I have so far:

Public Sub SearchEngine()
'Finds the location of an engine based on a EDC number input'

Dim edc As Integer
Dim xcol As Integer
Dim xrow As Integer
xcol = 1
xrow = 1

edc = InputBox("Enter the EDC number")
Do Until Cells(xrow, xcol).Value = edc
    Cells(xrow, xcol).Select
    If ActiveCell.Value = edc Then
        MsgBox ("Engine Model = " & ActiveCell.Offset(0, 1).Value)
    Else
        MsgBox ("This EDC number does not exist")
        Exit Do
    End If
    xrow = xrow + 1
Loop
End Sub

 

[Jaydip]

Hello there,

I am trying to write some VBA code that will loop through the first column of a sheet until the value of the cell matches the value of a variable previously stored from an inputbox. Then return the value of the cell next to it, in a message box.
I just started using VBA so I am not sure how to debug correctly, or how to find my error. Help is appreciated. Thank you.

This is what I have so far:

Public Sub SearchEngine()
'Finds the location of an engine based on a EDC number input'

Dim edc As Integer
Dim xcol As Integer
Dim xrow As Integer
dim flag as boolean
xcol = 1
xrow = 1

edc = InputBox("Enter the EDC number")
Do Until Cells(xrow, xcol).Value = edc
    Cells(xrow, xcol).Select
    If ActiveCell.Value = edc Then
        MsgBox ("Engine Model = " & ActiveCell.Offset(0, 1).Value)
        flag=True
        Exit Do
   ' Else
        'MsgBox ("This EDC number does not exist")
        'Exit Do
    End If
    xrow = xrow + 1
Loop
if not flag then
   MsgBox ("This EDC number does not exist")
end if
End Sub

I fixed the code, let me know if it works for you.

 

[giankr]

I fixed the code, let me know if it works for you.

Thank you. I ended up figuring out that the else statement will trip the loop functionality. But, that extra if statement you added to the code definitely does the trick.

Thank you very much, it seems to be working great now.

 

[Jaydip]

Thank you. I ended up figuring out that the else statement will trip the loop functionality. But, that extra if statement you added to the code definitely does the trick.

Thank you very much, it seems to be working great now.

You are welcome :biggrin:

 

[douglasb]

Kind of unrelated, but I seem to always see this in VB code: why declare the variables at the top of the function, then assign values to them immediately after? I see this a lot in VB6 and VBA especially, but also somewhat in VB .NET.

Why not:

Public Sub SearchEngine()
'Finds the location of an engine based on a EDC number input'

Dim xcol As Integer = 1
Dim xrow As Integer = 1
Dim flag As Boolean = False
Dim edc As Integer = InputBox("Enter the EDC number")

Do Until Cells(xrow, xcol).Value = edc
    Cells(xrow, xcol).Select
    If ActiveCell.Value = edc Then
        MsgBox ("Engine Model = " & ActiveCell.Offset(0, 1).Value)
        flag=True
        Exit Do
   ' Else
        'MsgBox ("This EDC number does not exist")
        'Exit Do
    End If
    xrow += 1
Loop

If Not flag Then
   MsgBox ("This EDC number does not exist")
End If

End Sub

Surely VBA allows declaration and initialization on the same line, or am I incorrect here? Is there another reason it's done this way?

 

[Jaydip]

Kind of unrelated, but I seem to always see this in VB code: why declare the variables at the top of the function, then assign values to them immediately after? I see this a lot in VB6 and VBA especially, but also somewhat in VB .NET.

Why not:

Public Sub SearchEngine()
'Finds the location of an engine based on a EDC number input'

[B]Dim xcol As Integer = 1[/B]
Dim xrow As Integer = 1
Dim flag As Boolean = False
Dim edc As Integer = InputBox("Enter the EDC number")

Do Until Cells(xrow, xcol).Value = edc
    Cells(xrow, xcol).Select
    If ActiveCell.Value = edc Then
        MsgBox ("Engine Model = " & ActiveCell.Offset(0, 1).Value)
        flag=True
        Exit Do
   ' Else
        'MsgBox ("This EDC number does not exist")
        'Exit Do
    End If
    xrow += 1
Loop

If Not flag Then
   MsgBox ("This EDC number does not exist")
End If

End Sub

Surely VBA allows declaration and initialization on the same line, or am I incorrect here? Is there another reason it's done this way?

IIRC declaration and initialization of varables in one line is not allowed in VBA.But this works

Public Const Test= 222

 

[Tweak155]

IIRC declaration and initialization of varables in one line is not allowed in VBA.But this works

Public Const Test= 222

That's exactly why. Only variables you can declare and assign on the same line are constants and optional parameters.