vba to copy one cell value to another cell value

danbates

Active Member
Joined
Oct 8, 2017
Messages
359
Office Version
  1. 2016
Platform
  1. Windows
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
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,896
Office Version
  1. 2010
Platform
  1. 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
359
Office Version
  1. 2016
Platform
  1. Windows
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,896
Office Version
  1. 2010
Platform
  1. 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
359
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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,896
Office Version
  1. 2010
Platform
  1. 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

Well-known Member
Joined
Mar 22, 2015
Messages
1,092
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

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

mohadin

Well-known Member
Joined
Mar 22, 2015
Messages
1,092
Office Version
  1. 2013
Platform
  1. 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
359
Office Version
  1. 2016
Platform
  1. Windows
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,136,260
Messages
5,674,681
Members
419,520
Latest member
Jennifer4Dillon

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top