Concat macro not working, error in coding

Question

My code seems to not be working, and I'm not sure why?

Sub Concat()

'Formula to combine the member AC# and Parish Name
Sheets("Risk Partner Data").Select
    Dim ACParish As String, i As String
    Dim rng As Range
    Set rng = Range("A" & Rows.Count).End(x1Up)
    ACParish = rng.Row
    For i = 2 To ACParish
        AcrtiveWorkbook.Sheets("Calc Data").Cells(i, 1) = Cells(i, 1) & Cells(1, 2)
    Next i
End Sub

Says that Compile error, Type mismatch and highlights the "i" in For i = 2

My objective: In another sheet (Risk Partner Data) I have Columns F & E, these are a mixture of text and numbers. I want it to run for all of the active cells in the columns.

I'm new to vba.

score 2 · Accepted Answer · 2018-09-11 22:20:50Z

2

i is being used as an integer in the For ... Next but you've declared it as a string; it should be a Long. Same for ACParish.

There is a typo in AcrtiveWorkbook.

You don't need to .Select a worksheet in order mto access it's values.

Should ... = .Cells(i, 1) & .Cells(1, 2) be ... = .Cells(i, 1) & .Cells(i, 2)?

Sub Concat()

'Formula to combine the member AC# and Parish Name
    Dim ACParish As long, i As long

    with workSheets("Risk Partner Data")
        ACParish  = .Range("A" & Rows.Count).End(xlUp).row
        For i = 2 To ACParish
            .parent.workSheets("Calc Data").Cells(i, 1) = .Cells(i, 1) & .Cells(1, 2)
        Next i
    end with

End Sub

edited Sep 11, 2018 at 22:20

answered Sep 7, 2018 at 4:17

user4039065

Sign up to request clarification or add additional context in comments.

4 Comments

Roaku Sezoire Over a year ago

Hi Jeeped, thank you. Could I please clarify - If my cells contain numbers and letters, is "i" OK to use? Also, if I wish for this to run in a specific column, how would I go about doing that??

user4039065 Over a year ago

i is a row number, not the value of a cell.

Roaku Sezoire Over a year ago

I'm getting an error for this "ACParish = .Range("E" & .Row.Count).End(x1Up).Row" what do I do?

user4039065 Over a year ago

x1Up should be xlUp

Collectives™ on Stack Overflow

Concat macro not working, error in coding

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related