Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter a formula in column A, if a condition in column "B" is met
Here is what I am after. I have a large download from a major retailer's
website. Unfortunately, it is not available in a format that is very user friendly to get information from. I am setting up a "database" worksheet to lookup all of the information off of the download, and put it into a format that can be used in a pivot table. What I need to do is evaluate column B, everywhere that STOCK#: ??????, (the ? is a wildcard) then enter a formula in column A. For instance: Evaluate column B Stock#: XXXXXX is found in cell B62 In cell A62 enter the formula =(B62&B60) Then continue on for 2500 rows repeating the procedure wherever "STOCK#: ?????) appears. I would like to thank everyone in advance for looking into this for me. Side note. If anyone knows of any good "Teach yourself VB" books or programs, please feel free to e-mail me. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter a formula in column A, if a condition in column "B" is met
In A62 put in the formula
=if(Left(trim(B62),7) = "STOCK#:",B62&B60,"") Then drag fill down (and/or up) column A. -- Regards, Tom Ogilvy "jeffbert" wrote in message ... Here is what I am after. I have a large download from a major retailer's website. Unfortunately, it is not available in a format that is very user friendly to get information from. I am setting up a "database" worksheet to lookup all of the information off of the download, and put it into a format that can be used in a pivot table. What I need to do is evaluate column B, everywhere that STOCK#: ??????, (the ? is a wildcard) then enter a formula in column A. For instance: Evaluate column B Stock#: XXXXXX is found in cell B62 In cell A62 enter the formula =(B62&B60) Then continue on for 2500 rows repeating the procedure wherever "STOCK#: ?????) appears. I would like to thank everyone in advance for looking into this for me. Side note. If anyone knows of any good "Teach yourself VB" books or programs, please feel free to e-mail me. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter a formula in column A, if a condition in column "B" is m
Thanks for your suggestion. Sorry it has been a few days, but just been extremely busy. I have a formula very similar to that already set up, and it works wonderfully. Let me explain the situation a little further. Every week I am sent numerous customer specific sales reports. What I would like to do is to create a central database, rather than saving each report by week. Ideally, i would like to open the database workbook, run a macro that evaluates a chosen file and column within that file. If a cell in this column is not blank, then copy the entire row to the next available row in the database. Thanks again Jeff Does anyone know any good books or programs to teach yourself VBA? "Tom Ogilvy" wrote: In A62 put in the formula =if(Left(trim(B62),7) = "STOCK#:",B62&B60,"") Then drag fill down (and/or up) column A. -- Regards, Tom Ogilvy "jeffbert" wrote in message ... Here is what I am after. I have a large download from a major retailer's website. Unfortunately, it is not available in a format that is very user friendly to get information from. I am setting up a "database" worksheet to lookup all of the information off of the download, and put it into a format that can be used in a pivot table. What I need to do is evaluate column B, everywhere that STOCK#: ??????, (the ? is a wildcard) then enter a formula in column A. For instance: Evaluate column B Stock#: XXXXXX is found in cell B62 In cell A62 enter the formula =(B62&B60) Then continue on for 2500 rows repeating the procedure wherever "STOCK#: ?????) appears. I would like to thank everyone in advance for looking into this for me. Side note. If anyone knows of any good "Teach yourself VB" books or programs, please feel free to e-mail me. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter a formula in column A, if a condition in column "B" is m
Sub GetData()
Dim col as Variant Dim fName as Variant Dim rng as Range, rng1 as Range Dim bk as Workbook fname = Application.GetOpenFileName() if fName < False then set bk = workbooks.Open(fName) else exit sub End if col = InputBox("Enter a column number or letter") set rng = workbooks("Master"). _ Worksheets("Data").Cells(rows.count,1).End(xlup)(2 ) with bk.worksheets(1) set rng1 = .cells(1,col).Entirecolumn.specialcells(xlConstant s) rng1.EntireRow.copy Destination:=rng End with bk.Close SaveChanges:=False End Sub -- Regards, Tom Ogilvy "jeffbert" wrote in message ... Thanks for your suggestion. Sorry it has been a few days, but just been extremely busy. I have a formula very similar to that already set up, and it works wonderfully. Let me explain the situation a little further. Every week I am sent numerous customer specific sales reports. What I would like to do is to create a central database, rather than saving each report by week. Ideally, i would like to open the database workbook, run a macro that evaluates a chosen file and column within that file. If a cell in this column is not blank, then copy the entire row to the next available row in the database. Thanks again Jeff Does anyone know any good books or programs to teach yourself VBA? "Tom Ogilvy" wrote: In A62 put in the formula =if(Left(trim(B62),7) = "STOCK#:",B62&B60,"") Then drag fill down (and/or up) column A. -- Regards, Tom Ogilvy "jeffbert" wrote in message ... Here is what I am after. I have a large download from a major retailer's website. Unfortunately, it is not available in a format that is very user friendly to get information from. I am setting up a "database" worksheet to lookup all of the information off of the download, and put it into a format that can be used in a pivot table. What I need to do is evaluate column B, everywhere that STOCK#: ??????, (the ? is a wildcard) then enter a formula in column A. For instance: Evaluate column B Stock#: XXXXXX is found in cell B62 In cell A62 enter the formula =(B62&B60) Then continue on for 2500 rows repeating the procedure wherever "STOCK#: ?????) appears. I would like to thank everyone in advance for looking into this for me. Side note. If anyone knows of any good "Teach yourself VB" books or programs, please feel free to e-mail me. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter a formula in column A, if a condition in column "B" is m
Thanks so much for the reply Tom. I am running into a "subscript out of
range" error. When I go to debug, the following two lines are highlighted: Set rng = Workbooks("Master"). _ Worksheets("Data").Cells(Rows.Count, 1).End(xlUp)(2) Thanks again "Tom Ogilvy" wrote: Sub GetData() Dim col as Variant Dim fName as Variant Dim rng as Range, rng1 as Range Dim bk as Workbook fname = Application.GetOpenFileName() if fName < False then set bk = workbooks.Open(fName) else exit sub End if col = InputBox("Enter a column number or letter") set rng = workbooks("Master"). _ Worksheets("Data").Cells(rows.count,1).End(xlup)(2 ) with bk.worksheets(1) set rng1 = .cells(1,col).Entirecolumn.specialcells(xlConstant s) rng1.EntireRow.copy Destination:=rng End with bk.Close SaveChanges:=False End Sub -- Regards, Tom Ogilvy "jeffbert" wrote in message ... Thanks for your suggestion. Sorry it has been a few days, but just been extremely busy. I have a formula very similar to that already set up, and it works wonderfully. Let me explain the situation a little further. Every week I am sent numerous customer specific sales reports. What I would like to do is to create a central database, rather than saving each report by week. Ideally, i would like to open the database workbook, run a macro that evaluates a chosen file and column within that file. If a cell in this column is not blank, then copy the entire row to the next available row in the database. Thanks again Jeff Does anyone know any good books or programs to teach yourself VBA? "Tom Ogilvy" wrote: In A62 put in the formula =if(Left(trim(B62),7) = "STOCK#:",B62&B60,"") Then drag fill down (and/or up) column A. -- Regards, Tom Ogilvy "jeffbert" wrote in message ... Here is what I am after. I have a large download from a major retailer's website. Unfortunately, it is not available in a format that is very user friendly to get information from. I am setting up a "database" worksheet to lookup all of the information off of the download, and put it into a format that can be used in a pivot table. What I need to do is evaluate column B, everywhere that STOCK#: ??????, (the ? is a wildcard) then enter a formula in column A. For instance: Evaluate column B Stock#: XXXXXX is found in cell B62 In cell A62 enter the formula =(B62&B60) Then continue on for 2500 rows repeating the procedure wherever "STOCK#: ?????) appears. I would like to thank everyone in advance for looking into this for me. Side note. If anyone knows of any good "Teach yourself VB" books or programs, please feel free to e-mail me. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter a formula in column A, if a condition in column "B" is m
Actually it should say
Set rng = Workbooks("Master.xls"). _ Worksheets("Data").Cells(Rows.Count, 1).End(xlUp)(2) But since you didn't provide any information on your particular files, I just made up names to be placeholders for you actual file and sheet names. Master.xls was to represent the workbook name of the file that holds your "database" and Data was to represent the sheet in Master.xls that holds your database. If those are not the actual names, and there is no reason they would be, then it would give you such an error until you replace them with the actual names. -- Regards, Tom Ogilvy "jeffbert" wrote in message ... Thanks so much for the reply Tom. I am running into a "subscript out of range" error. When I go to debug, the following two lines are highlighted: Set rng = Workbooks("Master"). _ Worksheets("Data").Cells(Rows.Count, 1).End(xlUp)(2) Thanks again "Tom Ogilvy" wrote: Sub GetData() Dim col as Variant Dim fName as Variant Dim rng as Range, rng1 as Range Dim bk as Workbook fname = Application.GetOpenFileName() if fName < False then set bk = workbooks.Open(fName) else exit sub End if col = InputBox("Enter a column number or letter") set rng = workbooks("Master"). _ Worksheets("Data").Cells(rows.count,1).End(xlup)(2 ) with bk.worksheets(1) set rng1 = .cells(1,col).Entirecolumn.specialcells(xlConstant s) rng1.EntireRow.copy Destination:=rng End with bk.Close SaveChanges:=False End Sub -- Regards, Tom Ogilvy "jeffbert" wrote in message ... Thanks for your suggestion. Sorry it has been a few days, but just been extremely busy. I have a formula very similar to that already set up, and it works wonderfully. Let me explain the situation a little further. Every week I am sent numerous customer specific sales reports. What I would like to do is to create a central database, rather than saving each report by week. Ideally, i would like to open the database workbook, run a macro that evaluates a chosen file and column within that file. If a cell in this column is not blank, then copy the entire row to the next available row in the database. Thanks again Jeff Does anyone know any good books or programs to teach yourself VBA? "Tom Ogilvy" wrote: In A62 put in the formula =if(Left(trim(B62),7) = "STOCK#:",B62&B60,"") Then drag fill down (and/or up) column A. -- Regards, Tom Ogilvy "jeffbert" wrote in message ... Here is what I am after. I have a large download from a major retailer's website. Unfortunately, it is not available in a format that is very user friendly to get information from. I am setting up a "database" worksheet to lookup all of the information off of the download, and put it into a format that can be used in a pivot table. What I need to do is evaluate column B, everywhere that STOCK#: ??????, (the ? is a wildcard) then enter a formula in column A. For instance: Evaluate column B Stock#: XXXXXX is found in cell B62 In cell A62 enter the formula =(B62&B60) Then continue on for 2500 rows repeating the procedure wherever "STOCK#: ?????) appears. I would like to thank everyone in advance for looking into this for me. Side note. If anyone knows of any good "Teach yourself VB" books or programs, please feel free to e-mail me. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Enter a formula in column A, if a condition in column "B" is m
Once again, Thank you. As mentioned at the end of my posts, I am trying to
teach this to myself, but not having much luck. I actually managed to figure how to get it to look at the second tab in the workbook instead of the first, which is a "Eureka" moment for me. Thanks again. "Tom Ogilvy" wrote: Actually it should say Set rng = Workbooks("Master.xls"). _ Worksheets("Data").Cells(Rows.Count, 1).End(xlUp)(2) But since you didn't provide any information on your particular files, I just made up names to be placeholders for you actual file and sheet names. Master.xls was to represent the workbook name of the file that holds your "database" and Data was to represent the sheet in Master.xls that holds your database. If those are not the actual names, and there is no reason they would be, then it would give you such an error until you replace them with the actual names. -- Regards, Tom Ogilvy "jeffbert" wrote in message ... Thanks so much for the reply Tom. I am running into a "subscript out of range" error. When I go to debug, the following two lines are highlighted: Set rng = Workbooks("Master"). _ Worksheets("Data").Cells(Rows.Count, 1).End(xlUp)(2) Thanks again "Tom Ogilvy" wrote: Sub GetData() Dim col as Variant Dim fName as Variant Dim rng as Range, rng1 as Range Dim bk as Workbook fname = Application.GetOpenFileName() if fName < False then set bk = workbooks.Open(fName) else exit sub End if col = InputBox("Enter a column number or letter") set rng = workbooks("Master"). _ Worksheets("Data").Cells(rows.count,1).End(xlup)(2 ) with bk.worksheets(1) set rng1 = .cells(1,col).Entirecolumn.specialcells(xlConstant s) rng1.EntireRow.copy Destination:=rng End with bk.Close SaveChanges:=False End Sub -- Regards, Tom Ogilvy "jeffbert" wrote in message ... Thanks for your suggestion. Sorry it has been a few days, but just been extremely busy. I have a formula very similar to that already set up, and it works wonderfully. Let me explain the situation a little further. Every week I am sent numerous customer specific sales reports. What I would like to do is to create a central database, rather than saving each report by week. Ideally, i would like to open the database workbook, run a macro that evaluates a chosen file and column within that file. If a cell in this column is not blank, then copy the entire row to the next available row in the database. Thanks again Jeff Does anyone know any good books or programs to teach yourself VBA? "Tom Ogilvy" wrote: In A62 put in the formula =if(Left(trim(B62),7) = "STOCK#:",B62&B60,"") Then drag fill down (and/or up) column A. -- Regards, Tom Ogilvy "jeffbert" wrote in message ... Here is what I am after. I have a large download from a major retailer's website. Unfortunately, it is not available in a format that is very user friendly to get information from. I am setting up a "database" worksheet to lookup all of the information off of the download, and put it into a format that can be used in a pivot table. What I need to do is evaluate column B, everywhere that STOCK#: ??????, (the ? is a wildcard) then enter a formula in column A. For instance: Evaluate column B Stock#: XXXXXX is found in cell B62 In cell A62 enter the formula =(B62&B60) Then continue on for 2500 rows repeating the procedure wherever "STOCK#: ?????) appears. I would like to thank everyone in advance for looking into this for me. Side note. If anyone knows of any good "Teach yourself VB" books or programs, please feel free to e-mail me. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I change the column heading in Excel to display "A" "B" "C | New Users to Excel | |||
Need the formula or macro. If i enter today date in the cell (Row 1,Column 2) and on tab out, the column 1 cell should be filled with "corresponding Day" of the date | Excel Discussion (Misc queries) | |||
Need Formula or macro. If i enter today date in the cell (Row 1,Column 2) and on tab out, the column 1 cell should be filled with "corresponding Day" of the date | Excel Discussion (Misc queries) | |||
how can I count if column A="active" and column E="Job" in a list? | Excel Worksheet Functions | |||
Use look-up table in Excel and enter into a "county" column, same | Excel Worksheet Functions |