hi,
Set ycell = xcell.Offset(0, 5)
ycell.Select 'this line not needed
amt = ycell.Value
question: what is in ycell? literally.
amt is set to integer. if a number is not in ycell.....problem. type mismatch.
interger is 2 bytes non-decimal.
regards
FSt1
:
This is the procedure:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim drw As String
Dim itemnum As Integer
Dim c As Integer
Dim xcell As Range
Dim ycell As Range
Dim costitem As String
Dim fcell As Range
Dim code As String
Dim costid As String
Dim watchrange As Range
Dim intersectrange As Range
Dim amt As Integer
'MsgBox ("event change")
On Error GoTo stoppit
Application.EnableEvents = False
unprotectsheet
stopautocalc
drw = ActiveSheet.Range("currentdraw").Value
itemnum = Range(drw & "itemnum").Value
Set watchrange = Range(drw & "itemsrng")
Set intersectrange = Intersect(Target, watchrange)
If intersectrange Is Nothing Then
MsgBox ("change outside of range")
Else
For c = 1 To itemnum
Set xcell = Range(drw & "costitem").Offset(c, 0)
costitem = xcell.Value
With Range("drwcostitemrng")
Set fcell = .find(what:=costitem, LookIn:=xlValues,
LookAt:=xlWhole, _
SearchOrder:=xlByColumns)
End With
If fcell Is Nothing And costitem <> "" Then
MsgBox ("c= " & c)
MsgBox ("Cost Item '" & costitem & "' does not exist." & vbLf
& _
"Select another Cost Item.")
GoTo stoppit
End If
Next
'MsgBox ("start cleardrw")
cleardrw
'MsgBox ("finished cleardrw")
For c = 1 To itemnum
'MsgBox ("c= " & c)
Set xcell = Range(drw & "itemno").Offset(c, 0)
costitem = xcell.Offset(0, 4).Value
If costitem <> "" Then
With Range("drwcostitemrng")
Set fcell = .find(what:=costitem, LookIn:=xlValues,
LookAt:=xlWhole, _
SearchOrder:=xlByColumns)
code = fcell.Offset(0, 1).Value
costid = fcell.Offset(0, 2).Value
End With
xcell.Offset(0, 2).Value = code
xcell.Offset(0, 3).Value = costid
End If
Next
startautocalc
For c = 1 To itemnum
MsgBox ("c= " & c)
Set xcell = Range(drw & "itemno").Offset(c, 4)
costitem = xcell.Value
MsgBox ("xcell = " & costitem)
Set ycell = xcell.Offset(0, 5)
ycell.Select
amt = ycell.Value
MsgBox ("amt =" & amt)
If costitem = "" And amt <> 0 Then
xcell.Select
MsgBox ("Select Cost Item before entering an amount.")
xcell.Offset(0, 5).Value = 0
GoTo stoppit
End If
Next
enteritem
End If
stoppit:
startautocalc
Application.EnableEvents = True
protectsheet
End Sub
I tried 'amt = ycell.value' and I still don't get amt equal to ycell.
:
hi,
try something like this.
Sub codeit()
Dim amt As Integer
Dim tcell As Range
Set tcell = Range("A1")
amt = tcell.Value
MsgBox amt
End Sub
regards
FSt1
:
I can't seem to get a variable to get the value from a cell. I have set 'amt
as integer', 'tcell as range'. 'tcell' is formated as currency on the
spreadsheet. I am trying to get amt to equal the value in 'tcell', but it is
not working. I have tried:
amt = tcell,
amt = range(tcell) and
amt = range(tcell).value
none of those seem to work. What am I doing wrong?
Thanks
