Macro for empty cell

[swarren]

How do you refer to an empty cell in vb? ("")

 

[farmerscott]

swarren,

Correct. Cells(x,1).value="" clears the cell or it has no value.

Watch that you do not put Cells(x,1).value=" " (note the extra space). That puts a space in the cell and can cause further problems.

Or are you asking to test if the cell is empty? Use something like the function LEN(A1).

Hope that helps,

FarmerScott

 

[swarren]

Thanks FarmerScott,
I am new to vb. I have 4 spreadsheet n excel. I copy and paste from the first one to the others and I recorded a macro. How do I tell it to stop at a blank cell the continue to the end? Here is my macro:
Range("B2:B8,D2:I8").Select
Range("D2").Activate
Selection.Copy
Sheets("Document").Select
Range("D2").Select
ActiveSheet.Paste
Range("A2").Select
Application.CutCopyMode = False
Selection.Copy
Range("A3:A8").Select
ActiveSheet.Paste
Range("A2:A8").Select
Application.CutCopyMode = False
Selection.Copy
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Input").Select
Range("D2:E8,J2:M8").Select
Range("J2").Activate
Application.CutCopyMode = False
Selection.Copy
Sheets("Transaction").Select
Range("D2").Select
ActiveSheet.Paste
Range("A2").Select
Application.CutCopyMode = False
Selection.Copy
Range("A3:A8").Select
ActiveSheet.Paste
Range("A2:A8").Select
Application.CutCopyMode = False
Selection.Copy
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Document").Select
Range("B2:J8").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("UploadAuto").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Transaction").Select
Range("B2:C8,F2:I8").Select
Range("F2").Activate
Application.CutCopyMode = False
Selection.Copy
Sheets("UploadAuto").Select
Range("A8").Select
ActiveSheet.Paste

 

[farmerscott]

I am unsure how your comment, "How do I tell it to stop at a blank cell the continue to the end?", fits into your code.

Your code seems to do a lot of copying and pasting to other sheets. What are the names of all your sheets including the 'master' sheet.

Can you give a description of what you want the code to do outlining each step and where the data resides.
A small copy of your data would be helpful.

cheers

Farmerscott

 

[swarren]

1st sheet=input, 2nd =document 3rd transaction & upload. I have a transaction sheets I get daily from different companies each varies with amount if data. I am trying to get this information combined on to the last spreadsheet (input) to upload into my acct. system. The doc sheet is one level of my entry screen and the trans sheet is another level, then I combine the 2 sheets to my input sheet and upload. I hope this explains it better because I don't know how to attach copies of the worksheets to this. Thanks for your time.

 

[farmerscott]

swarren,

Sorry I have been busy and not getting back to you on this....

So if I have this correct you want to combine the information on the sheet "Document" and sheet "Transactions" onto sheet "Input"?

I need to get a series of precise steps that you want the code to do..for example

1. Copy from sheet (??) in the ranges "B2:B8" and "D2:I8" to sheet (??) in the range of (??)
2. When pasting over to sheet(??), it will have data in columns (??). The column with the longest data is ??
3.
4. etc

If you can click on "Go Advanced" down the bottom right of your screen. Use the table function to build a copy of your data you want copied and another of what it will look like (on the pasted sheet).

For example-

With sheet1, I want to copy range "A1" and range "B2:B3"

	Col A	Col B
1	12345
2		54321
3		9876

<TBODY>
</TBODY>

I then want to copy to sheet 2 underneath existing data

	Col A	Col B
1	3456
2		5678
3		6789
4	12345
5		54321
6		9876
7

<TBODY>
</TBODY>

This will make writing the code much easier.

thanks

FarmerScott