Excel 2003 Macro Writing Help Needed

[TekiTek]

Hi there I am new to tek-tips so I hope my question will make sense to everyone...I am using Excel 2003 and have an extensive set of workbooks capturing stats for work. One of the workbooks needs to automatically copy certain information(located in a range of data), PasteSpec Values only at the first "empty" cell: starting at a certain cell and if that cell has a value to skip and perform action on the next specified cell if empty.

In more basic terms I need to write a macro that:

-> Run "copy" macro ((I have a macro that copies the needed info))
-> Find first empty cell:
From cell ___,(if filled then) cell _____,(if filled then) cell ____, (if filled then) cell ___
-> At first empty cell Paste Spec, Values
-> End

Any help would be amazing! Thank you in advance!!

 

[xlbo]

Have a look at the FAQs for finding the next empty cell

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[TekiTek]

Okay, I think I either don't understand the solution or I haven't explained my problem very well. The solution in the FAQs doesnt seem like it will take the information and place it in a pre-designated cell if empty only on command.

I have three columns and several rows, these rows are seperated into three parts.

Example: Column A:1-10 is Part1, 11-20 Part2, and 21-30 Part 3, Culumn B and C are also parted in the same way.

The coppied information needs to start in A:1 and if full it needs to go to the next empty cell that I pre-designate until it reaches the end at C:21.

So, I need to write a Macro that I can say paste this info in A:1, if full, go to A:11, and so forth. I have taught myself Excel and am still learning so please forgive any ignorance.

 

[SkipVought]

Please post the VBA code that you ar having a problem with.

Keep in mind that this is not Tek-we-provide-free-code.

If you are just learning Excel, you probably need to continue to increase your education in that respect. Trying to code an excel procedure will be much harder, if you are not familiar with basic and intermediate Excel functionality.

Along the way, you might consider recording and using macros, in order to observe how VBA looks and what it does.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[TekiTek]

I haven't had formal training in Excel and I have taught myself from the ground up. However I am familar with using VBA and Macros, recording Macs, and revising VBA to have Excel do what I need it to. At this point I have them automaticaly emailing, feeding info from workbook to workbook, clearing sheets, ect ect...so I've managed to teach myself alot about it.

However, I can't seem to make sense out of this b/c I'm not even sure where to start. Loops won't work, If statements in Macros seem to be what I want to use but I'm not sure exactly how. So I don't have any VBA code as of yet. Do you have any book/website suggestion that would lead me in the right direction for this particular issue? If the content is over my head, that's okay, I'll teach myself the basics of it to figure it out. I just need a good starting point.

Thanks for your patience and help!

 

[SkipVought]

There are posting in this forum similar to...

"One of the workbooks needs to automatically copy certain information(located in a range of data), PasteSpec Values only at the first "empty" cell: starting at a certain cell and if that cell has a value to skip and perform action on the next specified cell if empty"

Again, please post code that represents your starting point. You obvoiusly have alot of code that you have written.

Break it down into steps.

Copy a range

Find the last cell

Paste.



Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[xlbo]

ok - I get what you mean now. How are you going to be "designating" the next cell to go to. What is the logic involved ? Is there a lookup table ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[msstrang]

Modify the below code:

Range("a1").Copy

Set ExcelLastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)
' Determine the last row with data in it
LRWD = ExcelLastCell.Row

'replace the number in bold below with the column number your data is in.
Cells(LRWD + 1, [b]1[/b]).Select
ActiveSheet.Paste

if you need help understanding what is happening, put "a" in row 1/column1, "b" in row 2/column1, "c" in row 3/column1

 

[TekiTek]

Thank you very much for the replies, they were extremely helpful! However I have 2 questions.

First, I understand the basics of the above code and have revised it somewhat to work on a practice sheet, however when I move it into my "action workbook" and revise where necessary it stops working and I'm not sure why.

This is the revised code that works, (I need it to only enter values):

Range("a1").Copy

Set ExcelLastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)
' Determine the last row with data in it
LRWD = ExcelLastCell.Row
Cells(LRWD + 1, 6).Select
Selection.PasteSpecial paste:=xlPasteValues
Range("A1").Select
Application.CutCopyMode = False
'
End Sub

This is the revised code that does not work, (it stops working at "copy"):

Range("u3:u28").Copy

Set ExcelLastCell = ActiveSheet.Cells.SpecialCells(xlLastCell)
' Determine the last row with data in it
LRWD = ExcelLastCell.Row
Cells(LRWD + 1, 3).Select
Selection.PasteSpecial Paste:=xlPasteValues
Range("u3:u28").Select
Application.CutCopyMode = False
'
End Sub

Second, I need it to start in C:5 or column 3 row 5, I need it to skip action on rows 31 and 32, and I also need it to end action on column 3, row 114 and repeat code again on column 7.

->though I understand the code I don't think I understand it well enough to decifer how to tell it not to start in Column 3, row 3 and instead in coumn 3 row 5?

->in order to tell it to skip action on rows 31 & 32 I think I need to add a line like this:

(obviously the below is not the correct language...but the general idea)

perform action on C:31=False
Perform action on C:32=False

Am I headed in the wrong direction with this train of thought?

->to have it stop and perform action on a different column I think I might need it to "End with" and repeat the code using the new column/row numbers.

Again, am I headed in the wrong direction with this as well?

Any kind of advice would be helpful! Thanks again for all your patience and help!