Replies

Hans, Thanks. I’ll give it a try.

Hans, You are correct. It’s purpose is to link back to the Employee table and the “alias” was my way of making a copy to which to refer back. I don’t know how to use the same table twice, so this was my solution. It’s just a copy of the Employee table.

Hans, I thought I’d send you the structure of the database that I fnally got to work with your help. Maybe someone else will benefit from my lesson and as a way of saying “thank you”.

Hans,

Thanks for this. BTW, on a completely other note….I was tasked yesterday with creating a module that finds a cell and if the cell below it is the same (a duplicate), it should delete the row below it. I didn’t think I could do it, but I created the module and it worked. I am so excited and it’s all because of the great advice and help I’ve received here at the lounge. THANK YOU ALL

Thank you Rudi. I’ve passed this on to the user.

Hans,

Both.

Steve, Hats off to you, my man. This worked like a charm. It looks quite complicated and I will try to understand it.

Thank you all for your patience and TGIF, as well.

Pass along the thanks to Hans also. Being a trainer and in technology, I know it’s frustrating when a user can’t communicate properly.

Steve,

I attached a file that depicts a “before” and “after” to help demonstrate what I’m trying to accomplish. Sorry for the confusion. I’m working on deciphering (for my own learning) and putting your code into my personal.xls file to run.

Hans and Steve, There is a structure: Market and Advertiser are in the same column, with advertiser under market. I want to create 2 new columns: one for the advertiser (moving it from the column on the left and bringing it up one row) and another column for the date. Now the money and salesperson need to come up to put all the fields in the same row. After that, I need to delete the blank rows.

I appreciate your trying to sort out my thinking process and willingness to help me. I’m sorry that I’m not being as clear as I need to be. Any guidance would be appreciated.

Hans, Sorry. It’s late and I must be having several brain farts. Here’s the file.

Hans,

Thanks for taking the time. As you can see from my post above, I show the code of the loop. Attached is a sample of the spreadsheet. Basically, the advertiser needs to be on the same row as the Market, copy the date down, and then put the money and salesperson on the same line as the other data for the record.

Hans,

After reviewing my methodolgy, I found that I began in the wrong place. The loop is as follows:

Range(“A4”).Select
Selection.Cut Destination:=Range(“B3”)
Rows(“4:5”).Select
Selection.Delete Shift:=xlUp
Range(“A5”).Select
Selection.Cut Destination:=Range(“B4”)
Rows(“5:6”).Select
Selection.Delete Shift:=xlUp
Range(“A6”).Select
Selection.Cut Destination:=Range(“B5”)
Range(“B5”).Select

Basically what I am doing is moving contents of a cell to the right (-1) and above (-1) the current cell and then deleting 2 rows where data was, but is now blank.

Hans,

Yeah. Next i should have been on the next line. When I got it in the email, Next i was on the same line right after the last line that reads: Rows…..Delete Shift:=xlUp

Anyway, I put it on the next line and now it runs, however, I’m losing all of the data.

Could you explain the “For i ” instruction and the Rows (i & “:” &(i+1).Delete Shift:=xlUp

If this doesn’t work, I’ll send you the whole macro and small spreadsheet as a last resort.

Hans,

The last line appears in red and I get a message when I run it that it has a syntax error.

Quite right, Hans. I always forget that Zed is a number .

As for D2:E2, you can ignore. This is part of another (one-time) sequence. The loop begins when the pattern develops.

Thank you.