×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

VB CODE TO ADD FORMULA TO COLUMN USING HEADERS

VB CODE TO ADD FORMULA TO COLUMN USING HEADERS

VB CODE TO ADD FORMULA TO COLUMN USING HEADERS

(OP)
I have two columns ("Number" & "ROB#") and depending on who runs a program that gives me the excel info, these two columns can be in different locations.
IE... "Number" Column may be in row "C" or it may be in row "F" or "I".

Manually I have been using the formula ..... =if(C2=X2,".","XXX") .... Then I will drag it down the 1000 cells to check the info in both columns through the entire sheet. I use the "." instead of "" because if I see the "." it lets me know the formula is present and working and is extremely distinguishable from the XXX that lets me now something is wrong.

I have a section of code already working for inserting a column before the Number column. (Lines 47 thru 52 in the file attached.
I dont know how to add the formula to the new column having it compare the information in the other two columns with the above mentioned headers and then filling the formula down that column for as long as there is data available.

I apologize for not adding the excel file. It is filled with a lot of data that is considered GM property and I could get in trouble for handing it out. Privacy issues and such.


Thank you in advance for any help I receive.

RE: VB CODE TO ADD FORMULA TO COLUMN USING HEADERS

Hi,

Very confusing!

Are you saying that different workbooks have different data structures? That is "Number" and "ROB#" will be in adjacent columns in that order, but that the columns may be C:D or F:G or I:J?

I would NAME the ranges on the fly and then use the range names to perform whatever analysis or data manipulation is required, but this approach will require some knowledge of the structure of the data on your sheet. Where is this data on the sheet relative to other data on the sheet. I will not look at your code in order to divine what your sheet looks like. A small representative sample worksheet is required.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VB CODE TO ADD FORMULA TO COLUMN USING HEADERS

(OP)
There are over twenty different columns of data. Most of them useless to us. Depending on who runs the data from the program they are using, a few of the columns will be skipped. Which means the two columns I wish to compare can be under different lettered columns depending on who runs the info for me. If a column between them is skipped, the two columns of info might have 10 columns between them, or twenty columns between them or anything in between.

The problem is I cannot say how many differences will be between one file I get or another. So I need the vb to strictly look for the two cells to compare by searching for the headers "Number" and "ROB#". The only things that can be counted on to be consistent is the header names and the info would always be in the same rows with each other. So for example, row 25 under "Number" is supposed to be the same as row 25 under "ROB#".

In the attached file, I copied the header row from a real file and just tossed a bunch of bogus info in the cells below. Most all of it the same stuff because it doesnt matter for the purpose. For your benefit, I colored the two columns I need to compare but they will never be colored for me when i get the real info.

Every time we get this info, nothing is formatted and quite often there are problems with the info contradicting itself. I dont know if it is human error causing it or a glitchy program because I dont know what is entailed at their end. Every time I get these files, I have to go through and hide a bunch of columns, insert a column and put a formula in it to compare the two columns in question, format the width of columns I use and center the text to make it easier to follow. I tend to make certain columns bold as well just to make it easier for my eyes to follow certain info. It only take 5 or 10 minutes to do, so I never bothered making code for it before. However, now I am teaching others to do some of my work and trying to keep things done the same, I thought I would create code to do all of this for us. Then they just have to insert the .bas file to their new excel files we receive and run it and its always the same setup at the end to work with. I have got most of it figured out bit by bit, but I need to add a few bits yet.

1) I believe I have it inserting a blank column next to STA# column, but I cant figure out how to make code add the formula to those cells, to compare two columns when I dont know where the two columns I need to compare will fall from file to file.

2) I need to add code (which is already added to the file) called "=Material_Code" to columns I have inserted before each "Guage" column and make it interpret the "Material" Columns. Again, I dont know where these columns will fall from file to file. ..... I figured if I get the answer to problem 1), I can manipulate it to take care of this one. Note: The Material_Code formula will not work because I am not using the real codes for the info listed in the columns. Another reason I figured I would have to do it after finding the help for 1).

RE: VB CODE TO ADD FORMULA TO COLUMN USING HEADERS

I'd convert your table to a Structured Table (Insert > Tables > Table. Be sure to change the default table name, Table1, to something meaningful to this table like tQWD.

Now you can reference fields (columns) by name amd use those names in formulas.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VB CODE TO ADD FORMULA TO COLUMN USING HEADERS

(OP)
I myself, am not very knowledgeable with creating tables. But if I was to go this direction, will VB code be able to convert the file to using a table automatically? Will adding a table interfere with all of the other formatting in this code? Right now, I am to the point of just needing to add formula to specific columns. Will this make me start all over creating this VB code?

What is important to understand is that I do not have a ton of time to constantly be doing all of the formatting for each of these files coming out for everyone who is using them now. There are two or three others now who I am teaching to work on WESS (a program GM insists we use but hasnt updated since 2003) None of them know excel very well at all. For them to format the files and add tables and such it allows for a lot of wasted time and a ton if human error. That is why I am trying to make this automated. Everyone who needs to do it, can just run one macro and it formats everything. Then if someone else needs to hop in and make changes to it later, when they open the excel file, it will be just like every other one we work with.

If you say tables is the better way to go, I wont argue that. But with how close I have this VB code to the finished product and knowing I am working with people I have to teach the basics of excel to, not to mention in some cases there is a slight language barrier problem, is it really worth changing at this point?

RE: VB CODE TO ADD FORMULA TO COLUMN USING HEADERS

(OP)
To be completely honest, and this will show how unfamiliar with tables, but I dont see the power in them really. Other than the other row color formatting, it feels like it has just been filtered. I know there is a lot more to them, but not being familiar with them, I will need to learn more about them and the benefits before Tables are really any more help for me.

Since I have seen them mentioned a few times in the past here, I am thinking I do need to learn more about them. lol

Not being very good with VB code to begin with, I am going to have to play with this to see how I can incorporate it with what I have done and such and if it causes any conflicts with what I have and such.

I do appreciate the help. Just a little worried that it is another batch of learning on top of what I have already been putting myself through and I am trying to do it around my usual work. lol

RE: VB CODE TO ADD FORMULA TO COLUMN USING HEADERS

Put a formula into ANY table row in an empty column, and the formula will propagate to all rows of the table and each new row added to the table.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VB CODE TO ADD FORMULA TO COLUMN USING HEADERS

(OP)
Kind of funny. I was just looking at the code you had in it and trying to understand the range and how it knew to keep going down the column with the formula. I was wondering if it automatically added it to the rest of the column in the table and I saw the email pop up that you replied and found the exact answer. lol

I am starting to see how to do this. Something I will have to play with through the day between jobs and stuff.

Thanks.

RE: VB CODE TO ADD FORMULA TO COLUMN USING HEADERS

ST is one of the Version 2007 enhancements that is most significant.

You will see that as you enter a formula, the IntelliSense feature gives you choices that you can Select and then TAB to bring that selection into your formula. For instance...
...type in the formula bar...

=if

...and TAB. The result is...

=IF(


Then type...

IIF(tQ

...select tQWD and TAB. Then type an OPEN BRACKET [, and see what happens.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VB CODE TO ADD FORMULA TO COLUMN USING HEADERS

(OP)
Hey Skip

I had some time late here to mess with this. I took the macro in the file you gave me and copied it to a module in a different file and when I ran it, it gave me an error.

Run-Time error '1004':
Method 'Range' of object'_Global' failed


When I hit debug, it hilighted the section ....

ActiveSheet.ListObjects.Add(xlSrcRange, Range(Cells(1, 1).CurrentRegion), , xlYes).Name = _
"Table1"

RE: VB CODE TO ADD FORMULA TO COLUMN USING HEADERS

If you already have a Table1 in your workbook, you'll get an error.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VB CODE TO ADD FORMULA TO COLUMN USING HEADERS

Okay this procedure assures that the table name will be unique...

CODE

Sub AddStructuredTable()
'SkipVought 2017 Aug 7
'Makes the table on the ActiveSheet a Structured Table
'
    Dim lo As ListObject, ws As Worksheet, iCnt As Integer, bHasListObject As Boolean

    For Each ws In Worksheets
        For Each lo In ws.ListObjects
            If Not lo Is Nothing Then
            'counts ListObjects in the active workbook
                iCnt = iCnt + 1
                If ws.Name = ActiveSheet.Name Then
                    If Not Intersect(lo.Range, ws.UsedRange) Is Nothing Then
                    'determins if the table on the active sheet is a Structured Table
                        bHasListObject = True
                    End If
                End If
            End If
        Next
    Next

    With ActiveSheet
        If Not bHasListObject Then
        'make active sheet table a Structured Table
            .ListObjects.Add(xlSrcRange, .UsedRange, , xlYes).Name = "Table" & iCnt + 1
            With .Cells(1, 1).End(xlToRight)
            'put formula in a new table column to the far right
                .Offset(0, 1).Value = "formula"
                .Offset(1, 1).Formula = "=IF([Number]=[ROB '#],""."",""XXX"")"
            End With
        End If
    End With
End Sub 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: VB CODE TO ADD FORMULA TO COLUMN USING HEADERS

(OP)
This works well. Thanks. I was also successfully able to add it to the code I have put together to get it to hide all the columns and format font and such.

However, now I cant seem to figure out how to add a second formula to another existing column. I have spent a while today trying to look it up rather than keep bothering you but am not succeeding so far.

RE: VB CODE TO ADD FORMULA TO COLUMN USING HEADERS

(OP)
I figured out how to add more columns to the end and add the formula I want to them. I just cant figure out how to make that formula appear before column "Guage 1".

I wouldnt care if the formula is added to an existing column or a new column is added there for the formula. Just so the column with the new formula comes out before "Guage 1" column.

RE: VB CODE TO ADD FORMULA TO COLUMN USING HEADERS

(OP)
Ok, its probably not the right way but it works and IMO, anything that works is right. lol

I found code that will re-order columns. So I just created more columns at the end with the formula I wanted in it, then re-ordered all the columns to my desire.

I have another question now, but it is not about formulas so I will add a new thread for it....


Thanks for all your help. clown

RE: VB CODE TO ADD FORMULA TO COLUMN USING HEADERS

Great! Glad to help from the comfort of my sunroom.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close