Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

code syntax help 3

Status
Not open for further replies.

mscallisto

Technical User
Jun 14, 2001
2,990
US
Ho do I shorten the the line of code that reads:
Code:
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 ?

Code:
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
 




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


Skip,

[glasses] [red][/red]
[tongue]
 
Is that shorter?

_________________
Bob Rashkin
 

You do know that when you declare:
Code:
Dim usedrows, usedcolumns As Integer
it is the same as
Code:
Dim usedrows As Variant
Dim usedcolumns As Integer

usedrows is NOT an Integer in your declaration.

Have fun.

---- Andy
 
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
 
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
 
As I stated, this code
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:
Code:
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
 
Thanks kindly Andy...good info and good advice

sam
 

Just to add a little about standards.
I would suggest using pre-fixes in the names of your variables to indicate type:
Code:
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
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top