CODEWITHSUNDEEP
excelvba
Rob
Rob

Reputation: 428

Merge two Cells into One with VBA

I have an excel that I'm trying to make helper columns with for comparisons and searches made up of two cells in the same row. The Main Error I have with doing this is a Run-time error '438': Object doesn't support this property or method. The Second Error I know will be present is that Cell 3 is in string format and has Prefixed 0s. I was able to remove them with a formula by changing the Number Format to "Number" before concatenating but I'm unsure how to do that in a Macro.

I know if I was to do it manually in excel as a formula it would look like this: =CONCAT(NUMBERVALUE(C#),D#)

hrow = HSheet.Cells(Rows.Count, "A").End(xlUp).Row
For J = 2 To hrow
    HSheet.Range("F" & J) = Application.VLookup(HSheet.Range("D" & J), PP.Range("D2:L" & lastrow), 9, False)
    HSheet.Range("I" & J).Cell.Value = HSheet.Range("C" & J).Cell.Value & HSheet.Range("D" & J).Cell.Value
Next J

An Example of the data in my Cells in case it matters: 

Cell C   |  Cell D  |    Cell I   |
 0032    | AB975050 |  32AB975050 |

Upvotes: 0

Views: 61

Answers (1)

urdearboy
urdearboy

Reputation: 14580

You are using Range.Cell.Value when you should just be using Range.Value

HSheet.Range("I" & J).Value = HSheet.Range("C" & J).Value & HSheet.Range("D" & J).Value

Upvotes: 1

Related Questions