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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,073
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,073
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,073
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
 

Forum statistics

Threads
1,089,547
Messages
5,408,881
Members
403,240
Latest member
AlenKovacevic

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top