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

Note that we give Conversion a range. It gets the row number and puts it in
the variable Roww.
--
Gary''s Student - gsnu200769


"Rodney Crow" wrote:

Okay, maybe I'm not explaining enough about the macro. The macro
(Conversion) is activated when a dropdown box is clicked. Based upon that
choice, "Conversion" converts the choice for comparison (in this case, cost
per ounce). My code in "Conversion" uses If/Then/ElseIf statements based on
the cell that is clicked. For example:

If Cells(Row, "F").Value = " " Then
Cells(Row, "H").Value = "0.00 "
ElseIf Cells(Row, "F").Value = "BAG" Then
myVar = Val(InputBox("What is the size of the bag in pounds?", "Ounces"))
Cells(Row, "H").Value = Cells(Row, "G").Value / (myVar * 16)

and so on and so forth.

So what I need to do, is when say cell "f6" is clicked, it sends the row
value to my macro as the declared integer "Row" so that it will work for
every cell. I know there are probably many reading this that are rolling
their eyes at my brainlessness, but as I said, this is my first trial with
VBA.

Thank you all in advance for your help and suggestions.

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

"Rodney Crow" wrote:

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

Note that we give Conversion a range. It gets the row number and puts it in
the variable Roww.
--
Gary''s Student - gsnu200769


"Rodney Crow" wrote:

Okay, maybe I'm not explaining enough about the macro. The macro
(Conversion) is activated when a dropdown box is clicked. Based upon that
choice, "Conversion" converts the choice for comparison (in this case, cost
per ounce). My code in "Conversion" uses If/Then/ElseIf statements based on
the cell that is clicked. For example:

If Cells(Row, "F").Value = " " Then
Cells(Row, "H").Value = "0.00 "
ElseIf Cells(Row, "F").Value = "BAG" Then
myVar = Val(InputBox("What is the size of the bag in pounds?", "Ounces"))
Cells(Row, "H").Value = Cells(Row, "G").Value / (myVar * 16)

and so on and so forth.

So what I need to do, is when say cell "f6" is clicked, it sends the row
value to my macro as the declared integer "Row" so that it will work for
every cell. I know there are probably many reading this that are rolling
their eyes at my brainlessness, but as I said, this is my first trial with
VBA.

Thank you all in advance for your help and suggestions.

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


"Rodney Crow" wrote:

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

Note that we give Conversion a range. It gets the row number and puts it in
the variable Roww.
--
Gary''s Student - gsnu200769


"Rodney Crow" wrote:

Okay, maybe I'm not explaining enough about the macro. The macro
(Conversion) is activated when a dropdown box is clicked. Based upon that
choice, "Conversion" converts the choice for comparison (in this case, cost
per ounce). My code in "Conversion" uses If/Then/ElseIf statements based on
the cell that is clicked. For example:

If Cells(Row, "F").Value = " " Then
Cells(Row, "H").Value = "0.00 "
ElseIf Cells(Row, "F").Value = "BAG" Then
myVar = Val(InputBox("What is the size of the bag in pounds?", "Ounces"))
Cells(Row, "H").Value = Cells(Row, "G").Value / (myVar * 16)

and so on and so forth.

So what I need to do, is when say cell "f6" is clicked, it sends the row
value to my macro as the declared integer "Row" so that it will work for
every cell. I know there are probably many reading this that are rolling
their eyes at my brainlessness, but as I said, this is my first trial with
VBA.

Thank you all in advance for your help and suggestions.

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

"Joel" wrote:

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


"Rodney Crow" wrote:

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

Note that we give Conversion a range. It gets the row number and puts it in
the variable Roww.
--
Gary''s Student - gsnu200769


"Rodney Crow" wrote:

Okay, maybe I'm not explaining enough about the macro. The macro
(Conversion) is activated when a dropdown box is clicked. Based upon that
choice, "Conversion" converts the choice for comparison (in this case, cost
per ounce). My code in "Conversion" uses If/Then/ElseIf statements based on
the cell that is clicked. For example:

If Cells(Row, "F").Value = " " Then
Cells(Row, "H").Value = "0.00 "
ElseIf Cells(Row, "F").Value = "BAG" Then
myVar = Val(InputBox("What is the size of the bag in pounds?", "Ounces"))
Cells(Row, "H").Value = Cells(Row, "G").Value / (myVar * 16)

and so on and so forth.

So what I need to do, is when say cell "f6" is clicked, it sends the row
value to my macro as the declared integer "Row" so that it will work for
every cell. I know there are probably many reading this that are rolling
their eyes at my brainlessness, but as I said, this is my first trial with
VBA.

Thank you all in advance for your help and suggestions.

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.

"Joel" wrote:

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

"Joel" wrote:

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


"Rodney Crow" wrote:

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

Note that we give Conversion a range. It gets the row number and puts it in
the variable Roww.
--
Gary''s Student - gsnu200769


"Rodney Crow" wrote:

Okay, maybe I'm not explaining enough about the macro. The macro
(Conversion) is activated when a dropdown box is clicked. Based upon that
choice, "Conversion" converts the choice for comparison (in this case, cost
per ounce). My code in "Conversion" uses If/Then/ElseIf statements based on
the cell that is clicked. For example:

If Cells(Row, "F").Value = " " Then
Cells(Row, "H").Value = "0.00 "
ElseIf Cells(Row, "F").Value = "BAG" Then
myVar = Val(InputBox("What is the size of the bag in pounds?", "Ounces"))
Cells(Row, "H").Value = Cells(Row, "G").Value / (myVar * 16)

and so on and so forth.

So what I need to do, is when say cell "f6" is clicked, it sends the row
value to my macro as the declared integer "Row" so that it will work for
every cell. I know there are probably many reading this that are rolling
their eyes at my brainlessness, but as I said, this is my first trial with
VBA.

Thank you all in advance for your help and suggestions.