Why variables won't retrieve values

Pookiemeister

Well-known Member
Joined
Jan 6, 2012
Messages
563
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Code:
Sub Test()


Dim ws_count As Integer, i As Integer, finalRow As Integer, x As Integer
Dim Rng As Range


lDz = 0
lCs = 0
sUOM = " "


With ActiveWorkbook.Worksheets(formTitle)
    Set Rng = .Range("B2", .Range("B" & Rows.Count).End(xlUp))
End With


Call findItem(Rng, sPrdCde)


    lDz = Cells(rngItem.Address, 4)
    lCs = Cells(rngItem.Address, 5)
    sUOM = Cells(rngItem.Address, 6)


    If lDz = 0 Or lCs = 0 Or sUOM = "" Then
        Call ErrorTrap
    End If
    
    Chattfrm.Show
End Sub

Code:
Function findItem(Rng As Range, sPrdCde As String) As String
    
    
    Set rngItem = Rng.Find(sPrdCde, lookat:=xlPart)
    If Not rngItem Is Nothing Then
        MsgBox "Found at " & rngItem.Address
    End If


End Function

The problem I am having is that variables lDz, lCs, sUOM don't return any values even though there are values there to be retrieved. Would the problem that I am having have anything to do with the fact that rngItem.Address returns an absolute cell reference?
Code:
    lDz = Cells(rngItem.Address, 4)    lCs = Cells(rngItem.Address, 5)
    sUOM = Cells(rngItem.Address, 6)

Thank You
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
You haven't declared or set rngItem anywhere in the code.

If you actually want to use the function findItem to set rngItem the code would look more like this.
Code:
Sub Test()
Dim Rng As Range
Dim ws_count As Long, i As Long, finalRow As Long, x As Long

    lDz = 0
    lCs = 0
    sUOM = " "

    With ActiveWorkbook.Worksheets(formTitle)
        Set Rng = .Range("B2", .Range("B" & Rows.Count).End(xlUp))
    End With

    Set rngitem = findItem(Rng, sPrdCde)
    
    If Not rngitem Is Nothing Then
        lDz = Cells(rngitem.Address, 4)
        lCs = Cells(rngitem.Address, 5)
        sUOM = Cells(rngitem.Address, 6)
    Else
        MsgBox "Product code not found!"
        Exit Sub
    End If
    
    If lDz = 0 Or lCs = 0 Or sUOM = "" Then
        Call ErrorTrap
    End If

    Chattfrm.Show
    
End Sub

Function findItem(Rng As Range, sPrdCde As String) As Range
        
    Set findItem = Rng.Find(sPrdCde, lookat:=xlPart)

End Function
 
Upvote 0
Thank you for your help. rngItem was declared and I'm sorry that include it in the code. I declared it as Public and under Option Explicit, my apologies. So after I replaced my code with yours I get a runtime error 13 Type mismatch. The error occurs here on this line of code.
Code:
lDz = Cells(rngItem.Address, 4)
Here is all the code again. Thank you again.
Code:
Option Explicit

Public lDz As Long, lCs As Long
Public sUOM As String, sPrdCde As String, formTitle As String
Public rngItem As Range




Sub Test()


Dim ws_count As Long, i As Long, finalRow As Long, x As Long
Dim Rng As Range


lDz = 0
lCs = 0
sUOM = " "


With ActiveWorkbook.Worksheets(formTitle)
    Set Rng = .Range("B2", .Range("B" & Rows.Count).End(xlUp))
End With


Set rngItem = findItem(Rng, sPrdCde)




'Call findItem(Rng, sPrdCde)


'ws_count = ActiveWorkbook.Worksheets.Count
'
'    For i = 3 To ws_count
'        Worksheets(i).Activate
'        FinalRow = Cells(Rows.Count, 2).End(xlUp).Row
'        For x = 1 To FinalRow
'            Cells(x, 2).Select
'            If Cells(x, 2).Value = sPrdCde Then
'               lDz = Cells(x, 4)
'               lCs = Cells(x, 5)
'               sUOM = Cells(x, 6)
'               'Chattemfrm.txtbxStckNum.Value = Cells(x, 7)
'            End If
'        Next x
'    Next i
    If Not rngItem Is Nothing Then
        lDz = Cells(rngItem.Address, 4)
        lCs = Cells(rngItem.Address, 5)
        sUOM = Cells(rngItem.Address, 6)
    Else
        MsgBox "Product Code not found!"
        Exit Sub
    End If
    If lDz = 0 Or lCs = 0 Or sUOM = "" Then
        Call ErrorTrap
    End If
    
    Chattfrm.Show
End Sub


Function findItem(Rng As Range, sPrdCde As String) As Range
    
    Set findItem = Rng.Find(sPrdCde, lookat:=xlPart)
'    Set rngItem = Rng.Find(sPrdCde, lookat:=xlPart)
'    If Not rngItem Is Nothing Then
'        MsgBox "Found at " & rngItem.Address
'    End If


End Function
 
Upvote 0
REPOST:

Thank you for your help. rngItem was declared and I'm sorry that I didn't include it in the code. I declared it as Public and under Option Explicit, my apologies. So after I replaced my code with yours I get a runtime error 13 Type mismatch. The error occurs here on this line of code.
Code:
lDz = Cells(rngItem.Address, 4)
Here is all the code again. Thank you again.
Code:
Option Explicit

Public lDz As Long, lCs As Long
Public sUOM As String, sPrdCde As String, formTitle As String
Public rngItem As Range




Sub Test()


Dim ws_count As Long, i As Long, finalRow As Long, x As Long
Dim Rng As Range


lDz = 0
lCs = 0
sUOM = " "


With ActiveWorkbook.Worksheets(formTitle)
    Set Rng = .Range("B2", .Range("B" & Rows.Count).End(xlUp))
End With


Set rngItem = findItem(Rng, sPrdCde)

    If Not rngItem Is Nothing Then
        lDz = Cells(rngItem.Address, 4)
        lCs = Cells(rngItem.Address, 5)
        sUOM = Cells(rngItem.Address, 6)
    Else
        MsgBox "Product Code not found!"
        Exit Sub
    End If
    If lDz = 0 Or lCs = 0 Or sUOM = "" Then
        Call ErrorTrap
    End If
    
    Chattfrm.Show
End Sub


Function findItem(Rng As Range, sPrdCde As String) As Range
    
    Set findItem = Rng.Find(sPrdCde, lookat:=xlPart)

End Function
 
Upvote 0
Is the Runtime error occurring because rngItem.Address is an absolute cell reference? Thank you.
 
Upvote 0
The syntax for Cells is this,

Cells(row, column)

where row is the row number for the cell you want to refer to and column is the column number, or letter, of the cell you want to refer to.
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,202
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top