Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
I have a worksheet I import a text file into and then remove certain
columns and format the sheet. The problem I am having is that the las row always has data in it I don't need. I need to know how to find the last row with data and then delete it through VBA code. The second problem is I have a column (column E) with error codes in it and I want to be able to go through every cell in that column and for each error code put the error reason in column F next to that row. I was thinking of looping through the rows in column E but was not sure how to. Any help would be appreciated. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
Colb
To get the last row with data and delete (assuming that there will always be data in column A Rows(Range("a65536").End(xlUp).Row).Selec Selection.Delete Shift:=xlU To cycle through column E (again it assumes that there will be data in every cell Range("e1").selec while not isempty(activecell activecell.offset(0,1).value = .... activecell.offset(1,0).selec wen To get the value of the error code, there are various ways to do it. One way is to have a lookup table somewhere in the workbook and then use worksheetfunction.vlookup to get the description and put in in the next column. This would replace the ... above. Or you could put in a vlookup formula into column F and just copy it down and let it calculate. This would chagne the line to be activecell.offset(0,1).formula = "=vlookup(.....) Ton ----- colby s wrote: ---- I have a worksheet I import a text file into and then remove certai columns and format the sheet. The problem I am having is that the la row always has data in it I don't need. I need to know how to find th last row with data and then delete it through VBA code. The secon problem is I have a column (column E) with error codes in it and want to be able to go through every cell in that column and for eac error code put the error reason in column F next to that row. I wa thinking of looping through the rows in column E but was not sure ho to. Any help would be appreciated |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
Thanks Tony!
The delete last row with data worked great. I am still a little confused on how to write the code to incorporate the worksheetfunction.vlookup. I understand about the table I would have to make but how it would be written, I don't know. Maybe it is just me? Also, I forgot to mention that the error codes are three characters long and there may be more than one in a cell. EX: error code would be 047141 So the error codes would be 047 & 141. Is there a way to pull these out without an array? Is there a way to use a wildcard like * ? Thanks Again acw wrote in message ... Colby To get the last row with data and delete (assuming that there will always be data in column A) Rows(Range("a65536").End(xlUp).Row).Select Selection.Delete Shift:=xlUp To cycle through column E (again it assumes that there will be data in every cell) Range("e1").select while not isempty(activecell) activecell.offset(0,1).value = ..... activecell.offset(1,0).select wend To get the value of the error code, there are various ways to do it. One way is to have a lookup table somewhere in the workbook and then use worksheetfunction.vlookup to get the description and put in in the next column. This would replace the ... above. Or you could put in a vlookup formula into column F and just copy it down and let it calculate. This would chagne the line to be activecell.offset(0,1).formula = "=vlookup(.....)" Tony ----- colby s wrote: ----- I have a worksheet I import a text file into and then remove certain columns and format the sheet. The problem I am having is that the las row always has data in it I don't need. I need to know how to find the last row with data and then delete it through VBA code. The second problem is I have a column (column E) with error codes in it and I want to be able to go through every cell in that column and for each error code put the error reason in column F next to that row. I was thinking of looping through the rows in column E but was not sure how to. Any help would be appreciated. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro Help
Colby
On your workbook generate a matrix which has the codes in one column, and the descriptions in the next. Name that range "codess". On thing, make sure that the codes are numeric. When I tested this, I had the lookup table in the same workbook as the data. You may have to modify the code to nominate the workbook that contains the lookup table. I'm assuming that the data that comes in is in text form, so that if there is a code which starts with a 0, then the 0 will be visible. That way, each code is 3 characters long. The code to parse each cell into a 3 char string, then look it up in the lookup table, and output it in the subsequent columns is: For i = 1 To Len(ActiveCell) / 3 ActiveCell.Offset(0, i).Value = WorksheetFunction.VLookup(Val(Mid(ActiveCell, i * 3 - 2, 3)), Range("codess"), 2, False) Next i Note that there is a mix of text and value here. That is why I specified that the lookup table had to be numbers, and the input values have to be text. Text is required to ensure that the data is of the correct length. I've converted the text to the value to get the lookup to work easily. Tony |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
Macro Help Needed - Excel 2007 - Print Macro with Auto Sort | Excel Worksheet Functions | |||
Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
Start Macro / Stop Macro / Restart Macro | Excel Programming |