code syntax help 3

[mscallisto]

Ho do I shorten the the line of code that reads:

Print #1, .Cells(i, 1); "|"; .Cells(i, 2); "|"; .Cells(i, 3); "|"; .Cells(i, 4)

e.g. replace the 1 2 3 4 with a variable representing j=1 to usedcolumns ?

Sub pipey()
Dim usedrows, usedcolumns As Integer

Open "C:\Documents and Settings\sam\Desktop\Pipey.txt" For Output As #1

With Worksheets(1).Range("a:d")
  usedrows = ActiveSheet.UsedRange.Rows.Count
  usedcolumns = ActiveSheet.UsedRange.Columns.Count

  For i = 1 To usedrows
    Print #1, .Cells(i, 1); "|"; .Cells(i, 2); "|"; .Cells(i, 3); "|"; .Cells(i, 4)
  Next i
End With
Close #1
MsgBox ("Done Successfully")
End Sub

 

[JerryKlmns]

Nest an extra For ... Next loop

 

[SkipVought]

Hi,

sPrint=""
for j = 1 to 4
  sPrint = sPrint & .Cells(i, j)
  if j < 4 then sPrint = sPrint &  "|"
next
Print #1, sPrint

Skip,

_[red][/red]

 

[Bong]

Is that shorter?

_________________
Bob Rashkin

 

[SkipVought]

The LINE is shorter! ;-)

Skip,

_[red][/red]

 

[Andrzejek]

You do know that when you declare:

Dim usedrows, usedcolumns As Integer

it is the same as

Dim usedrows As Variant
Dim usedcolumns As Integer

usedrows is NOT an Integer in your declaration.

Have fun.

---- Andy

 

[PHV]

Perhaps something like this ?
...
For i = 1 To usedrows
For j = 1 To usedcolumns - 1
Print #1, .Cells(i, j); "|";
Next j
Print #1, .Cells(i, usedcolumns)
Next i
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mscallisto]

Thanks everyone that's exactly what I needed.

PHV's is the most direct method and I like simplicity!

I do like Skips "build as you go" method also but if I remember correctly, Skip is a Red Sox fan and what do they know? LOL

To answer Andrzejek, no I didn't know that and I guess I don't know the rule that governs that...can you explain?

thanks again
sam

 

[SkipVought]

Damn the Rocket!

Skip,

_[red][/red]

 

[SkipVought]

...and George!

Skip,

_[red][/red]

 

[Andrzejek]

As I stated, this code

Dim usedrows, usedcolumns As Integer

will give you usedrows As Variant

If you want to keep a one line of declaration of more than one variable, do:

Dim usedrows [blue]As Integer[/blue], usedcolumns [blue]As Integer[/blue]

You have to state the type of variable

Your original line of declaration is valid in .NET, but VBA and VB classic requires type of Variable.

Myself, I like to declare one variable per line. it helps MZTools to find the ones I do not use any more.

Have fun.

---- Andy

 

[mscallisto]

Thanks kindly Andy...good info and good advice

sam

 

[Andrzejek]

Just to add a little about standards.
I would suggest using pre-fixes in the names of your variables to indicate type:

Dim [blue]int[/blue]UsedRows As Integer, [blue]int[/blue]UsedColumns As Integer

It helps when you are looking at the code to know what type of var it is.

So when I see strSomething I know it is a string, dblSum is a Double, blnIsDone is either True or False (Boolean), etc. (BTW, the use of 'is' in Boolean's name makes it easier to know what's going on.)

Camel (animal, not the cigaretes ) type helps reading names better, like strWhatDidYouHaveForLunch is a lot easier to read than strwhatdidyouhaveforlunch


Have fun.

---- Andy

 

[mscallisto]

Thanks Andy

I agree and for serious code (not one time 10 minute specials) I usually try to adhere to structured code practices.

again thanks for the tip