vba to copy one cell value to another cell value

danbates

Board Regular
Joined
Oct 8, 2017
Messages
240
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
 

offthelip

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

Board Regular
Joined
Oct 8, 2017
Messages
240
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,007
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

Board Regular
Joined
Oct 8, 2017
Messages
240
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,007
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

Board Regular
Joined
Oct 8, 2017
Messages
240
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,082,300
Messages
5,364,384
Members
400,796
Latest member
vrcdesktop

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top