Macro Help

  • Thread starter Thread starter colby s
  • Start date Start date
C

colby s

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.
 
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
 
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 said:
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.
Click to expand...
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(.....)"
 
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
 
Back
Top