• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to Insert a column based on a condition?

Steve Brown

New Member
Namaste Chandoo bhai and all:

See attached file. In there, say I am in cell D4.

From there I want to add a column to left if A1= "Add_Colm" is met.

If however, B2= "Move_Rt" condition is met, then I want to move to next right cell (E5) as my chosen/selected cell.

I know use of IF function, but cannot figure how to do the above; and also do not know alternative easy way to do it.

Since more than a decade I have not done C++ or visual basic; so asking.
 

Attachments

  • HowTo_sb.xlsx
    7.9 KB · Views: 4
Steve

Firstly, Welcome to the Chandoo.org Forums

See if this does what you want

You can't actually insert columns without VBA
Unless you mean use formulas to shift the data to the right
But seeing as there was no data i assumed the previous
 

Attachments

  • HowTo_sb.xlsm
    16.2 KB · Views: 6
What I noticed is that if I select a column, and then run the macro,
then a blank column is inserted to the left of the selected column.

If I select a cell, and run the macro,
then a cell is added to the left.

This may not help for my problem.
Tomorrow I will describe my actual problem; feeling sleepy.
 
The attached file also includes description of the automaton problem. A major part of it, I think, is a macro for adding a column to left of the active cell if a condition has met; or just make the next right cell (or next to next right cell) an active cell (with another macro), where the automation will check if there is need to add a column to left of the active cell.

Please do not work on this puzzle unless you do it for fun or challenge. I am trying to sharpen my rusty VBA skills, and it is not going to make income for me. -- This is the first time I am learning interactively from a forum like this. Namaste!
 

Attachments

  • HowTo_sb1.xlsm
    18.1 KB · Views: 5
Hi Steve ,

See if this does what you want.

Test it with all possible variations , since I have tested it only on your worksheet in its present form.

Narayan
 

Attachments

  • HowTo_sb1_1.xlsm
    19.4 KB · Views: 6
Namaste Narayan ji,

Yes it adds columns for missing years. Thanks.

After a lot of struggle I was also able to do it, and also write year # and quarter # in their rows in the added columns. I used For loop.
I am working on adding a column for any missing quarter in any year.

While struggling, I could not find/figure a code for:

1) How to give/assign active (or selected) cell's column # or row # to a variable? I tried like "x = Column()", but that did not.

2) How I change active cell to a next (up, down, left, or right) cell or to a specific cell by code?

BTW, where are you? I am in USA.

jai sri krishna!
 
Hi Steve ,

Using ActiveCell.Row returns the row number ( between 1 and 1048576 ) of the active cell ; so also with ActiveCell.Column

Normally , it is neither necessary nor advisable to move the activecell , since you can do what ever you wish to do by directly referring to the cell ; for example :

Range("J17").Select

makes J17 the active cell.

Now , you can refer to any other cell using the Offset property ; in fact you can do the same even without selecting J17 ; thus where ever you are :

Range("J17").Offset(-1)

will refer to J16.

Range("J17").Offset(1)

will refer to J18.

Range("J17").Offset(,-1)

will refer to I17.

Range("J17").Offset(,1)

will refer to K17.

The usage Range("J17").Offset(,1) is the same as Range("J17").Offset(0,1).

You can even prefix the Range keyword with the Worksheets(....) object , to refer to a cell / range on a different worksheet.

I am in India.

Narayan
 
Narayan ji,

Thanks for informing about the useful VBA codes. I will use them.

What this tip "
  • Use
    Code:
     &
    tags to embed your VBA Macros
  • Upload a Sample File to get a quicker response
"
above the e-mail window mean? How to use it? put the code between
Code:
 and [CODE/]?

jai sri Krishna!
 
Hi Steve ,

The tags to be used for embedding VBA code within your post are :

[
CODE
]


and

[
/
CODE
]


I have typed them on separate lines since otherwise the editor will process them as tags. Whatever is highlighted in blue will be together when you use it in your post.

What ever text is inserted between these two tags will not be processed by the editor , but will be displayed as it is , in the sense that the editor will not remove tabs and spaces ; other than this , it will color keywords but that is not so significant ; since spaces will be preserved , you can present even tables in this fashion , since otherwise it will be difficult to follow which data is in which column , especially if the initial columns are blank. See the following example :

A B C D
--------------------------------
1 2 4
2 3
2 4
1 2 3 4


The above data is now embedded in the code tags below :
Code:
A          B          C          D
----------------------------------
1          2                     4
           2          3
           2                     4
1          2          3          4
Narayan
 
Thanks Narayan ji, now I know the use of
Code:
 and
, like:

Code:
 With Application
  .EnableEvents = False
  .ScreenUpdating = False
  End With
 
  Set Rng = [RangeToBeChecked]
  NumberofCells = Rng.Columns.Count
 
  yearcounter = 0
  nextyear = Rng.Cells(1, 2)

In the above codes you made, I would like to know:

- What does "With Application" code do? Are there a variety of 'With's?
- What does this do or mean: Set Rng = [RangeToBeChecked]?
- It looks like that some variables like NumberofCells, nextyear, inserted, counter, etc. are used without predefining them. Does VBA have a number of variable already predefined?

What VBA book you recommend I buy?

I found that I can learn fast by seeing an example, or when someone shows me how to do it. Microsoft help articles use software programmer's lingo which gives me a gist, and they write too much; so I like them less.

It seems that I can do a complex automation by:
1) recording a number of needed macros
2) designing a number of Sub routines for tasks for which I cannot record macros, and
3) designing a master Sub routine that will use both the above as needed.

What you say?

jai sri Krishna!
 
Hi Steve ,

Most of what you have mentioned regarding how to learn VBA is perfectly correct ; the only objection is that learning by trial and error is more time-consuming.

If you can start with a book , your pace of learning will be more , so that you can learn more in the same time.

Any book by John Walkenbach or Bill Jelen or any of the other Excel experts will do.

See if you can get a book which concentrates on developing working applications , since these will give you an idea of what practical programming involves.

To your other points :

With is a construct which is used to reduce the amount of typing that you need to do if you did not use it.

For example , some procedures might do the following before they start processing data :

1. Turn off screen refresh - Application.ScreenUpdating = False
2. Disable event triggering - Application.EnableEvents = False
3. Set the calculation mode to Manual - Application.Calculation = xlCalculationManual
4. Disable alert messages - Application.DisplayAlerts = False

As you can see , this involves typing out the keyword Application 4 times ; instead , if we use the With construct , we can get the same functionality using the following code :
Code:
With Application
     .ScreenUpdating = False
     .EnableEvents = False
     .Calculation = xlCalculationManual
     .DisplayAlerts = False
End With
Narayan
 
Hi Steve ,

In VBA coding , anything within square brackets [ and ] is a named range ; thus , in the example you have posted , RangeToBeChecked is a named range.

The usage of square brackets is again a short-form as follows :

Range("RangeToBeChecked").Value

Only named ranges can be used within square brackets.

Narayan
 
Back
Top