Macro Question revisited

R

Rodney Crow

I have a macro that works if I hard code the cell number into instead of
using the "t" variable as suggested below. If I use the t variable though,
nothing happens. I have put the first part of the code into the spreadsheet
code as suggested, and the macro "Conversion" into a module called
"ConversionModule". When I try to run the macro now though, nothing shows in
the selection box....what am I doing wrong here?????


It will not matter if F6 ( or any of F6 thru F65536) is changed thru direct
typing or via a data validation dropdown.
In the worksheet code area:
Private Sub Worksheet_Change(ByVal Target As Range)
Set r = Range("F6:F65536")
Set t = Target
If Intersect(t, r) Is Nothing Then Exit Sub
Application.EnableEvents = False
Call Conversion(t)
Application.EnableEvents = True
End Sub
and in a standard module:
Sub Conversion(t)
Roww = t.Row
If Cells(Roww, "F").Value = " " Then
Cells(Roww, "H").Value = "0.00 "
End If
End Sub
 
F

FSt1

hi
i'm suspicious of this line in conversion(t).....
If Cells(Roww, "F").Value = " " Then
the code is looking for a space in Cell(Roww,"F") and if it doesn't find a
space then it skips updating H.
sooo.....
try changing that to...
If Cells(Roww, "F").Value = ""
no space between the double quotes.

Regards
FSt1
 
J

Joel

I don't know why the code is so complicated

Private Sub Worksheet_Change(ByVal Target As Range)
if target.column >= 6 then
if Target = "" then
target = 0.0
end if
end if
end sub
 
J

Joel

If you are copying and pasting more than one cell you need to cycle through
all the target cells

Private Sub Worksheet_Change(ByVal Target As Range)
for each cell in target
if cell.column >= 6 then
if cell = "" then
cell = 0.0
end if
end if
next cell
end sub
 
R

Rodney Crow

The macro actually has more than just that one calculation. It contains
If/ElseIf statements that do a conversion based on a dropdown box of choices.
The first one looks for a blank (signifying no choice has been made). There
are then about 9 other choices the user can make, but I didnt not post them
all here in order to keep the post a bit shorter.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top