VB CODE TO ADD FORMULA TO COLUMN USING HEADERS 1

[Kenja824]

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.

 

[SkipVought]

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 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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kenja824]

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).

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kenja824]

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?

 

[SkipVought]

Here's your table returned with that conversion and the formula.

I didn't have time to pretty up the code for my macro recording. But it demonstrates the power of Structured Tables.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kenja824]

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

 

[SkipVought]

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kenja824]

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.

 

[SkipVought]

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...
[tt]
=if
[/tt]
...and TAB. The result is...
[tt]
=IF(
[/tt]

Then type...
[tt]
IIF(tQ
[/tt]
...select tQWD and TAB. Then type an OPEN BRACKET [, and see what happens.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kenja824]

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"

 

[SkipVought]

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

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

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,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Kenja824]

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.

 

[Kenja824]

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.

 

[Kenja824]

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. )

 

[SkipVought]

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

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]