vba to copy one cell value to another cell value

danbates

Active Member
Joined
Oct 8, 2017
Messages
261
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.

Code:
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
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,077
Office Version
2010
Platform
Windows
try this code:
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

Active Member
Joined
Oct 8, 2017
Messages
261
Hi,

I am getting a error 13 - type mismatch.

Code:
d4 = d2 + Range("A50").Value
any ideas?

Thanks

Dan
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,077
Office Version
2010
Platform
Windows
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?
 
Last edited:

danbates

Active Member
Joined
Oct 8, 2017
Messages
261
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

Well-known Member
Joined
Dec 23, 2017
Messages
1,077
Office Version
2010
Platform
Windows
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.
 
Last edited:

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
Hi
What about
Code:
Sub test()
    x = Split(ActiveCell.Value, " -")
    n = x(0) + Range("a50")
    ActiveCell.Value = n & " -"
End Sub
 

mohadin

Active Member
Joined
Mar 22, 2015
Messages
361
Office Version
2013
Platform
Windows
And if A50 text and number such as (145 xxx) then
Code:
Sub test()
    x = Split(ActiveCell.Value, " -")
    y = Split(Range("a50"), " ")
    n = CDec(x(0)) + CDec(y(0))
    ActiveCell.Value = n + n2 & " -"
End Sub
 

danbates

Active Member
Joined
Oct 8, 2017
Messages
261
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:
Code:
d4 = d2 + Range("A50").Value
Thanks again

Dan
 

Watch MrExcel Video

Forum statistics

Threads
1,089,790
Messages
5,410,432
Members
403,318
Latest member
mistro333

This Week's Hot Topics

Top