VBA Conditional loop

[ana_c]

Hello,



I'm trying to automate a Boolean process on Excel and was hoping to get some help with the loop/vba logic.



Column A contains integers that range between 1-4.

I would like to create 4 columns on the side (since "A" contains 4 possible values) which have the following logic:

If cell A2=2, B2=0, C2=1,D2=0, E2=0.

Similarly if A3=4, B2=0, C2=0, D2=0, E2=1.



So essentially I'd like to create a binary string in columns B, C, D and E based on the value in A.

I would like to do this for the entire column A



I want to be able to do this with a macro as I have many such columns (as "A) that I wish to automate and transform into binary columns (as B, C, D and E)



Please note that of all the columns I have, the value ranges are different, i.e. not all columns range from 1-4.. some of them are 1-2 and some are 1-10 but they are always integers. So the number of columns to create next to each principal column (from this example, principal column = A), will be equal to the max value of the principal column.



Thanks in advance for your help

Ana

 

[Joe4]

Try this variation:

Sub MyMacro()

    Dim n As Long
    Dim lc As Long, c As Long
    Dim rng As Range
    Dim lr As Long
    Dim mx As Long
   
    Application.ScreenUpdating = False
   
'   Find last column in row 1 with data
    lc = Cells(1, Columns.Count).End(xlToLeft).Column
   
'   Initalize column value
    c = 1
   
'   Loop through each principal column
    For n = 1 To lc
'       Find largest row with data in column
        lr = Cells(Rows.Count, c).End(xlUp).Row
'       Build column range
        Set rng = Range(Cells(2, c), Cells(lr, c))
'       Find largest value in column
        mx = Application.WorksheetFunction.Max(rng)
'       Insert appropriate number of columns, if greater than 1
        If mx > 1 Then
            Range(Cells(1, c + 1), Cells(1, c + mx)).EntireColumn.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
'           Insert formulas
            Range(Cells(2, c + 1), Cells(lr, c + mx)).FormulaR1C1 = "=IF(COLUMN()-" & c & "=RC" & c & ",1,0)"
'           Increment c for next set
            c = c + mx + 1
        Else
            c = c + 1
        End If
    Next n
   
    Application.ScreenUpdating = True
   
End Sub

 

[ana_c]

Many thanks. This works great

 

[Joe4]

You are welcome.

 

[ana_c]

Hi Joe4,

I'm trying to learn a bit more about conditional statements in macros. Can you help me adjust the above code based on a condition in a different sheet?

So on sheet1, I have data that looks like this

I have set up the text in column G so that it matches the column titles on sheet2 where I am running the macro that you helped me with in your previous reply. To refresh your memory, the original state of the data on sheet2 (i.e. before running the macro) looks like this:

I would like to run the macro on sheet2 based on the tag in sheet1 (column J). If J1 = 1 then run the macro on sheet2 for the variable name in cell G1 (on sheet1)


Hope that makes sense.

Thanks in advance

 

[Joe4]

That is really a new question, and as such should be posted to a new thread.
Then others will also see it as a new, unanswered question.