Please help with this... using a marco to modify cells

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

What i want to do is make a marco that changes or adds units to a cell, that
allows the userer to enter the units desired.(by inputbox)

heres what i was trying, but i can get it to work

Dim x As String
x = ActiveCell.Formula
y = InputBox("What units do you want to input?", ("Unit Type?"), " CY")
x = "=" & x & " " & y
ActiveCell.Formula = x

origonally i had
x = ActiveCell.value
y = InputBox("What units do you want to input?", ("Unit Type?"), " CY")
x = "=" & x & " " & y
ActiveCell.value = x

but this took the number and not ther equasion so that further changes on
the sheet didnt update the column.

i recorded a macro doing this and it didnt help me much.
Please help!


Thanks!
 
First of all, in setting X you probably need to use
X = ActiveCell.Value
rather than the formula. If it has a formula (or gets one when you use it
once) then the formula will already have an = symbol as part of it.

When you add the user input value to things, then it's going to change it
all to a string in the cell, so future math using it will have to be created
specially to deal with it, and it will get touchy to use (depending on
whether or not you've added some units identifier to it)
Assuming the cell in question is A1, then the piece to deal with it and use
it as a value in a formula you'd need something like
IF(FIND(" ",A1)>1,VALUE(LEFT(A1,FIND(" ",A1)-1)),A1)
so a formula to multiply that value by 2 would look like
=IF(FIND(" ",A1)>1,VALUE(LEFT(A1,FIND(" ",A1)-1)),A1) * 2
 
Back
Top