set variable while looping question

[Lumpy]

I've got a simple looping vba that someone here wrote that processes a list
of values into a txt file of g-code to run automated machinery.

I need to add some functionality to it. I need to lookup piece number and
orientation on another worksheet and return the two different orientation
values to two variables. Then i need to incorporate that value into my
concatentation that exports to a txt file.

Here's my data:

Worksheet = Boards
A B C
Bd# Pc# Lenght
1 101 487.4
1 102 284.3
1 103 119.5
2 104 699.3

Worksheet = Pieces
A B D E
Pc# Width Angle Orientation
101 88.1 45.0 Left
101 88.1 22.5 Right
102 88.1 -30.0 Left
102 88.1 15.0 Right
103 88.1 -20.0 Left
103 88.1 35.7 Right
104 88.1 45 Left
....

Here's my code:

Sub WriteToText()

'The file name that will be used
Dim sFileName As String
sFileName = "C:\temp\ExampleOuput.txt"

'File System Objects - Set a reference to "Microsoft Scripting Runtime"
'Under Tools - References
Dim fso As Scripting.FileSystemObject
Dim f As Scripting.TextStream

'Create the File System and Text Stream Objects
Set fso = New Scripting.FileSystemObject
Set f = fso.CreateTextFile(sFileName, True, False)

'Data Range Information to Process
Dim iRow, iCol
Dim wsWorkSheet
Dim wsRange As Range

Set wsWorkSheet = ThisWorkbook.Sheets("Boards")
Set wsRange = wsWorkSheet.Cells(1, 1).CurrentRegion ' Get the range of
Data

f.WriteLine "G28" 'Start the file with a G28 command

For iloop = 2 To wsRange.Rows.Count 'skilp the header row thus iLoop = 2
IboardNum = wsRange(iloop, 1)
f.WriteLine "G1 X" & wsRange(iloop, 3) & " M6"
If IboardNum <> wsRange(iloop + 1, 1) Then
f.WriteLine "G28"
End If
Next

f.Close

Set f = Nothing
Set fso = Nothing

End Sub



This works great and returns the expected
G28
G1 x487.4 M6
G1 x284.3 M6
G1 x119.5 M6
G28

I'd like the G1 lines to read:
G1 x487.4 y45 z22.5 M6
where y is the left angle value of the piece and z is the right angle value.

This is simplified a little, I need to do some trig calculations to the
right and left angle values and return that calculated value to the y and z
parts of the concatenation.

thansk for any help.

 

[Bob Phillips]

I hope that I have been able to test it properly

Sub WriteToText()
'The file name that will be used
Dim sFileName As String
sFileName = "C:\temp\ExampleOuput.txt"

'File System Objects - Set a reference to "Microsoft Scripting Runtime"
'Under Tools - References
Dim fso As Scripting.FileSystemObject
Dim f As Scripting.TextStream

'Create the File System and Text Stream Objects
Set fso = New Scripting.FileSystemObject
Set f = fso.CreateTextFile(sFileName, True, False)

'Data Range Information to Process
Dim iRow As Long, iCol As Long
Dim iLoop As Long
Dim iBoardNum As Variant
Dim wsWorkSheet As Worksheet
Dim wsRange As Range
Dim val1 As Variant, val2 As Variant
Dim f

Set wsWorkSheet = ThisWorkbook.Sheets("Boards")
Set wsRange = wsWorkSheet.Cells(1, 1).CurrentRegion ' Get the range of
Data

f.writeline "G28" 'Start the file with a G28 command

For iLoop = 2 To wsRange.Rows.Count 'skilp the header row thus iLoop = 2
iBoardNum = wsRange(iLoop, 1)
f.writeline "G1 X" & wsRange(iLoop, 3)
If iBoardNum <> wsRange(iLoop + 1, 1) Then
f = f & "G28" 'f.WriteLine "G28"
End If
val1 = "": val2 = ""
On Error Resume Next
val1 = ActiveSheet.Evaluate("=INDEX(Pieces!C1:C2000," & _
"MATCH(1,(Pieces!A1:A2000=Boards!B2)*" &
_
"(Pieces!D12000=""Left""),0))")
val2 = ActiveSheet.Evaluate("=INDEX(Pieces!C1:C2000," & _
"MATCH(1,(Pieces!A1:A2000=Boards!B2)*" &
_
"(Pieces!D12000=""Right""),0))")
On Error GoTo 0
f.writeline " y" & val1 & " z" & val2 & " M6"
Next

f.Close

Set f = Nothing
Set fso = Nothing

End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Bob Phillips]

Spotted a couple of problems

Sub WriteToText()
'The file name that will be used
Dim sFileName As String
sFileName = "C:\temp\ExampleOuput.txt"

'File System Objects - Set a reference to "Microsoft Scripting Runtime"
'Under Tools - References
Dim fso As Scripting.FileSystemObject
Dim f As Scripting.TextStream

'Create the File System and Text Stream Objects
Set fso = New Scripting.FileSystemObject
Set f = fso.CreateTextFile(sFileName, True, False)

'Data Range Information to Process
Dim iRow As Long, iCol As Long
Dim iLoop As Long
Dim iBoardNum As Variant
Dim wsWorkSheet As Worksheet
Dim wsRange As Range
Dim val1 As Variant, val2 As Variant

Set wsWorkSheet = ThisWorkbook.Sheets("Boards")
Set wsRange = wsWorkSheet.Cells(1, 1).CurrentRegion ' Get the range of
Data

f.WriteLine "G28" 'Start the file with a G28 command

For iLoop = 2 To wsRange.Rows.Count 'skilp the header row thus iLoop = 2
iBoardNum = wsRange(iLoop, 1)
f.WriteLine "G1 X" & wsRange(iLoop, 3)
If iBoardNum <> wsRange(iLoop + 1, 1) Then
f.WriteLine "G28"
End If
val1 = "": val2 = ""
On Error Resume Next
val1 = ActiveSheet.Evaluate("=INDEX(Pieces!C1:C2000," & _
"MATCH(1,(Pieces!A1:A2000=Boards!B" &
iLoop & ")*" & _
"(Pieces!D12000=""Left""),0))")
val2 = ActiveSheet.Evaluate("=INDEX(Pieces!C1:C2000," & _
"MATCH(1,(Pieces!A1:A2000=Boards!B" &
iLoop & ")*" & _
"(Pieces!D12000=""Right""),0))")
On Error GoTo 0
If IsError(val1) Then val1 = "" Else: val1 = " y" & val1
If IsError(val2) Then val2 = "" Else: val2 = " z" & val2
f.WriteLine val1 & val2 & " M6"
Next

f.Close

Set f = Nothing
Set fso = Nothing

End Sub


--
---
HTH

Bob


(there's no email, no snail mail, but somewhere should be gmail in my addy)