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.
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
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,
Just traded in my OLD subtlety...
for a NUance!
RE: VB CODE TO ADD FORMULA TO COLUMN USING HEADERS
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
Now you can reference fields (columns) by name amd use those names in formulas.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: VB CODE TO ADD FORMULA TO COLUMN USING HEADERS
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
I didn't have time to pretty up the code for my macro recording. But it demonstrates the power of Structured Tables.
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: VB CODE TO ADD FORMULA TO COLUMN USING HEADERS
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
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: VB CODE TO ADD FORMULA TO COLUMN USING HEADERS
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
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,
Just traded in my OLD subtlety...
for a NUance!
RE: VB CODE TO ADD FORMULA TO COLUMN USING HEADERS
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
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: VB CODE TO ADD FORMULA TO COLUMN USING HEADERS
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 SubSkip,
Just traded in my OLD subtlety...
for a NUance!
RE: VB CODE TO ADD FORMULA TO COLUMN USING HEADERS
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
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
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.
RE: VB CODE TO ADD FORMULA TO COLUMN USING HEADERS
Skip,
Just traded in my OLD subtlety...
for a NUance!