Value not matching

[Rpettis31]

I am running a loop to obtain values on a change event yet I can not seem to
obtain that value from the sheet I am looking at the values are returning a
blank?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim x As Integer

If Target.Address = "$C$13" Then
ItemNum = Target.Value

Sheets("Data").Select

For x = 1 To 10

If Cells(x, 1) = ItemNum Then
Desc = Cells(x, 2)
StdCost = Cells(x, 3)
Spec = Cells(x, 6)
End If

Next x

Sheets("Justification - working").Select

Cells(13, 4) = Desc
Cells(13, 6) = StdCost
Cells(13, 5) = Spec
End If

End Sub

 

[Rpettis31]

cells(x,1) is equals a "blank" yet there are values in the cells it is
looking at in the loop?

 

[Tom Hutchins]

Unqualified cell references in a worksheet module always refer to the sheet
containing the module. Does this version do what you want?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Integer
If Target.Address = "$C$13" Then
ItemNum = Target.Value
For x = 1 To 10
If Sheets("Data").Cells(x, 1) = ItemNum Then
Desc = Sheets("Data").Cells(x, 2)
StdCost = Sheets("Data").Cells(x, 3)
Spec = Sheets("Data").Cells(x, 6)
End If
Next x
Sheets("Justification - working").Cells(13, 4) = Desc
Sheets("Justification - working").Cells(13, 6) = StdCost
Sheets("Justification - working").Cells(13, 5) = Spec
End If
End Sub

Hope this helps,

Hutch

 

[Mike H]

Hi,

It's because it's worksheet code and your not qualifying the ranges with
which sheet that data values are located. Try this and note that we no
lionger select any sheets

Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Long
If Target.Address = "$C$13" Then
ItemNum = Target.Value
For x = 1 To 10
If Sheets("Data").Cells(x, 1) = ItemNum Then
With Sheets("Data")
Desc = .Cells(x, 2)
StdCost = .Cells(x, 3)
Spec = .Cells(x, 6)
End With
Exit For
End If
Next x
With Sheets("Justification - working")
.Cells(13, 4) = Desc
.Cells(13, 6) = StdCost
.Cells(13, 5) = Spec
End With
End If
End Sub


Mike