VBA Conditional loop

ana_c

New Member
Joined
May 26, 2021
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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
 
Try this variation:
VBA Code:
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
 
Upvote 0
Solution

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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
1623147155773.png


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:

1623147073733.png


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
 

Attachments

  • 1623146916443.png
    1623146916443.png
    6.8 KB · Views: 9
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,894
Messages
6,175,252
Members
452,623
Latest member
Techenthusiast

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top