vba to copy one cell value to another cell value

[danbates]

Hi,

I have this value (95551092 - ) in the activecell and I would like to add whatever value is in cell A50.

I do have the following code which adds the value but it deletes the spaces and then moves the hyphen in front of the numbers.

Sub test()

Dim d As Double


d = ActiveCell.Value


ActiveCell = d & Range("A50").Value


End Sub

Is there a way of adding the data and keeping the values the same?

Thanks

Dan

 

[offthelip]

try this code:

Sub test2()


Dim d As String
Dim temp As String
Dim d2 As String
Dim d3 As String
Dim d4 As Long




d = ActiveCell.Value
d2 = ""
d3 = ""
For i = 1 To Len(d)
  temp = Mid(d, i, 1)
  If IsNumeric(temp) And temp <> " " Then
    d2 = d2 & temp
  Else
    d3 = d3 & temp
  End If
Next i
d4 = d2 + Range("A50").Value
 temp = CStr(d4) & d3






ActiveCell = temp




End Sub

 

[danbates]

Hi,

I am getting a error 13 - type mismatch.

d4 = d2 + Range("A50").Value

any ideas?

Thanks

Dan

 

[offthelip]

what have you got in A50, I assumed it was just a number. Is it in the same format as the Active cell?
another thought do you have brackets around the number in the active cell?

 

[danbates]

Hi,

The Value in A50 is letters and numbers and no there isn't any brackets in the activecell value.
I just put them in the original post so you could see the last space.

Thanks

Dan

 

[offthelip]

If A50 is letters and numbers it will default to being a string so EXCEL can't add it to a number. You need to specify what the format of the "number" in A50 is. And then you need to manipulate in a similar way that I have done to the ACTIVE cell to extract the number and then add two numbers together. Then this raises the question of what you want done wtih the remains of the string in A50. you didn't specify your problem very well.

 

[mohadin]

Hi
What about

Sub test()
    x = Split(ActiveCell.Value, " -")
    n = x(0) + Range("a50")
    ActiveCell.Value = n & " -"
End Sub

 

[mohadin]

And if A50 text and number such as (145 xxx) then

Sub test()
    x = Split(ActiveCell.Value, " -")
    y = Split(Range("a50"), " ")
    n = CDec(x(0)) + CDec(y(0))
    ActiveCell.Value = n + n2 & " -"
End Sub

 

[danbates]

Hi mohadin,

Thank you for your codes.

Your first code moves the space and hyphen after what is entered in cell A50.
activecell value = 92911092
A50 value = 23W
combined = 9291109223W -

How I would like it to look = 92911092 - 23W

Second code gives a type mismatch error and highlights this line:

d4 = d2 + Range("A50").Value

Thanks again

Dan