Identifying and using Relative variable in excel VBA Macro?

Question

Alright, so I have a spreadsheet, and I want to use this code (and more) but I'm getting problems. I want to take row A and Find the [-x] I should be using (see RC[StartColumn]) and autofill the rest. I then want to be able to repeat the steps for columns A-AF.

So essentially I want to perform the first calculation below, and the second one right next to that, and I want to be able to start it anywhere on the document. I then want to be able to copy that down each row for every column leading up to the first cell I chose.

Problem is I'm incredibly new to Macros, I put that together by observing my record macro results and playing around a little, but this is becoming too advanced for my current level and I want to finish this by today if possible. Could someone please show me the way, in a way that could teach me so I could do this on my own in the future?

Sub ConcatenateStep1()

Dim StartColumn As Integer
StartColumn = 1 - ActiveCell.Column


ActiveCell.FormulaR1C1 = _
    "=IF(AND(RC14=R[1]C14),IF(OR(ISNUMBER(FIND(LOWER(RC[StartColumn]),LOWER(R[1]C[StartColumn]),1)),ISNUMBER(FIND(LOWER(R[1]C[StartColumn]),LOWER(RC[StartColumn]),1))),IF(LEN(RC[StartColumn])<LEN(R[1]C[StartColumn]),R[1]C[StartColumn],RC[StartColumn]),CONCATENATE(RC[StartColumn],"", "",R[1]C[StartColumn])),IF(AND(RC14<>R[1]C14,RC14<>R[-1]C14),RC[StartColumn],"" ""))"
Range("AK2").Select

Set SourceRange = Range("AK2")
Set fillRange = Range("AK2:AK22000")
SourceRange.AutoFill Destination:=fillRange

Range("AL2").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(RC14=R[1]C14,RC14<>R[-1]C14),IF(OR(R[1]C[-1]="" "",ISNUMBER(FIND(LOWER(RC[-1]),LOWER(R[1]C[-1]),1)),ISNUMBER(FIND(LOWER(R[1]C[-1]),LOWER(RC[-1]),1))),IF(LEN(RC[-1])<LEN(R[1]C[-1]),R[1]C[-1],RC[-1]),CONCATENATE(RC[-1],"", "",R[1]C[-1])),IF(AND(RC14<>R[1]C14,RC14<>R[-1]C14),RC[-1],"" ""))"

Set SourceRange = Range("AL2")
Set fillRange = Range("AL2:AL22000")
SourceRange.AutoFill Destination:=fillRange

Range("AL2").Select
End Sub

Answer 1

This is only a partial answer.

Are these incredibly long and complicated formulae the best way of achieving whatever you want to achieving?

There are two errors in the first formula that prevent it being accepted by Excel when the macro attempts to place it in a cell.

• The formula is a long string containing StartColumn. But StartColumn is a variable. You need to replace every occurence of StartColumn with " & StartColumn & ".

• Within a string each double quote required in the result must be replaced by two double quotes. Within CONCATENATE(RC[StartColumn],"", "",R[1]C[StartColumn]) the double quotes have not been doubled although those at the end of the formula have.

Excel will accept the following (which means it is syntactically correct) although I do not understand its purpose so it may be logically incorrect:

ActiveCell.FormulaR1C1 = _
    "=IF(AND(RC14=R[1]C14),IF(OR(ISNUMBER(FIND(LOWER(RC[" & StartColumn & _
    "]),LOWER(R[1]C[" & StartColumn & "]),1)),ISNUMBER(FIND(LOWER(R[1]C[" & _
    StartColumn & "]),LOWER(RC[" & StartColumn & "]),1))),IF(LEN(RC[" & _
    StartColumn & "])<LEN(R[1]C[" & StartColumn & "]),R[1]C[" & StartColumn & _
    "],RC[" & StartColumn & "]),CONCATENATE(RC[" & StartColumn & _
    "],"""", """",R[1]C[" & StartColumn & _
    "])),IF(AND(RC14<>R[1]C14,RC14<>R[-1]C14),RC[" & StartColumn & "],"" ""))"

If I have parsed this formula correctly, CONCATENATE(RC[StartColumn],"", "",R[1]C[StartColumn]) has the same output as CONCATENATE(RC[StartColumn],R[1]C[StartColumn])

I agree with MP24, you must clarify your question before I or someone else can help you further. The Macro Recorder records syntactically correct VBA but it is not good VBA. It does not know your objective so records individual keyboard actions when a skilled programmer would combine them. It is almost never a good idea to use Select or to work with the ActiveCell but the Macro Recorder has no choice but to do so.

Part 2

Although Excel allowed the macro to place the first forumula, it later complained the formula contains a circular reference. Its gone midnight here, I will look at it tomorrow.

Part 3

Having looked at your first formula again, I believe I incorrectly parsed it last night. I now believe the concatenate function is correct. Also it appears that the circular reference depends on the location of the ActiveCell when the macro starts. I was able to clear this error by selecting a different start position.

My current attempt to parse your formula gives:

Outer:  "=IF(X1,X2,X3)"
X1:     AND(RC14=R[1]C14)
X2:     IF(X4,X5,X6) 
X3:     IF(X7,RC[StartColumn],"" "")    
X4:     OR(X8,X9)
X5:     IF(X10,R[1]C[StartColumn],RC[StartColumn])
X6:     CONCATENATE(RC[StartColumn],"", "",R[1]C[StartColumn])
X7:     AND(RC14<>R[1]C14,RC14<>R[-1]C14)
X8:     ISNUMBER(X11)
X9:     ISNUMBER(X12)
X10:    LEN(RC[StartColumn])<LEN(R[1]C[StartColumn])
X11:    FIND(LOWER(RC[StartColumn]),LOWER(R[1]C[StartColumn]),1)
X12:    FIND(LOWER(R[1]C[StartColumn]),LOWER(RC[StartColumn]),1)

I have used macros to place formulae so they can later give changing results as the user enters values. Do you want changing results? If not, replacing this formula with VBA would almost certainly give more understandable and more maintainable code.

I do not approve of formulae like this. I am aware that many people use complex formulae successfully and are pleased with the results. However, I have spent too much of my life picking up the pieces left by over clever people who have moved on. How long does it take to get a formula like this correct? How long does it take to amend when six or twelve months later a slightly different result is required? Too often, in my experience, the person who has to amend it six or twelve months later is not the original author. With VBA the author can leave comments saying what the code is doing but with formulae the author cannot. Over clever authors don't always leave the comments they should but at least VBA code is usually easier to decode than a complex formula.