variable
 

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

variable
 

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
"ranswert" wrote:

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

variable
 

This is the procedu

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.

"FSt1" wrote:

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
"ranswert" wrote:

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

variable
 

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

"ranswert" wrote:

This is the procedu

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.

"FSt1" wrote:

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
"ranswert" wrote:

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

variable
 

I added some messages and selected some cells so that I could see what was
going on.

ycell is sometimes blank or it is a currency that can be any amount formated
with '.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"

Is there a better way to write this?
Thanks

"FSt1" wrote:

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

"ranswert" wrote:

This is the procedu

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.

"FSt1" wrote:

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
"ranswert" wrote:

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

variable
 

hi
do you get an error message? if so, what?
try dim ycell as single 'or double not integer

Regards
FSt1

"ranswert" wrote:

I added some messages and selected some cells so that I could see what was
going on.

ycell is sometimes blank or it is a currency that can be any amount formated
with '.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"

Is there a better way to write this?
Thanks

"FSt1" wrote:

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

"ranswert" wrote:

This is the procedu

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.

"FSt1" wrote:

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
"ranswert" wrote:

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

variable
 

I get an 'runtime error '6''
'overflow' message

I changed amt to single and that seemed to work. I will work on that and
make sure. What does single or double do?
Thanks for your help

"FSt1" wrote:

hi
do you get an error message? if so, what?
try dim ycell as single 'or double not integer

Regards
FSt1

"ranswert" wrote:

I added some messages and selected some cells so that I could see what was
going on.

ycell is sometimes blank or it is a currency that can be any amount formated
with '.NumberFormat = "$#,##0.00_);[Red]($#,##0.00)"

Is there a better way to write this?
Thanks

"FSt1" wrote:

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

"ranswert" wrote:

This is the procedu

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.

"FSt1" wrote:

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
"ranswert" wrote:

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