Need help with IF THEN STOP statement (& General)

[daveb994]

So basically i am running this macro to generate the coordinates of certain points on a piece of material. However once the outer edge of the material (the breadth=1000) has been plotted i need the macro to stop running (leave cells unchanged). Also i was wondering if there was any way to automatically generate a general code or if my code could be refined and made better.
B is breadth
L is length
W is strip width
S is starting point which will be (0,0)

All help is greatly appreciated

Sub Numbers()

Dim B_1 As Integer
Dim L_2 As Integer
Dim W_3 As String
Dim S_1 As String

N_B = 1000
N_L = 500
N_W = 250
S_1 = 0

Worksheets(2).Range("A2").Value = "X Coordinate"
Worksheets(2).Range("A3").Value = S_1
Worksheets(2).Range("A4").Value = S_1
Worksheets(2).Range("A5").Value = N_W
Worksheets(2).Range("A6").Value = N_W
Worksheets(2).Range("A7").Value = (2 * N_W)
Worksheets(2).Range("A8").Value = (2 * N_W)
Worksheets(2).Range("A9").Value = (3 * N_W)

Worksheets(2).Range("B2").Value = "Y Coordinate"
Worksheets(2).Range("B3").Value = S_1
Worksheets(2).Range("B4").Value = N_L
Worksheets(2).Range("B5").Value = S_1
Worksheets(2).Range("B6").Value = N_L
Worksheets(2).Range("B7").Value = S_1
Worksheets(2).Range("B8").Value = N_L
Worksheets(2).Range("B9").Value = S_1

If "A:" > (Range("K3").Value) Then
End

End If

End Sub

 

[StephenCrump]

Welcome to the Forum!

To generalise your code, you could write cell by cell:

Worksheets(2).Range("A" & i).Value = ...
Worksheets(2).Range("B" & i).Value = ...

where i is a loop counter.

Instead, the code below does all this in a VBA array, and writes once to Excel at the end of the looping. I'm guessing the output is close to what you're looking for?

Sub Numbers()

    Dim lX As Long, lXInc As Long, lXStart As Long
    Dim lY As Long, lYInc As Long, lYStart As Long
    Dim lNumbers() As Long, lRows As Long, lCols As Long
    Dim lcount As Long, i As Long, j As Long
    
    lX = 1000
    lY = 500
    lXStart = 0
    lYStart = 0
    lXInc = 250
    lYInc = 500
    lRows = Int(lX / lXInc) + 1
    lCols = Int(lY / lYInc) + 1
    ReDim lNumbers(1 To lRows * lCols, 1 To 2)

    For i = 1 To lRows
        For j = 1 To lCols
            lcount = lcount + 1
            lNumbers(lcount, 1) = lXStart + (i - 1) * lXInc
            lNumbers(lcount, 2) = lYStart + (j - 1) * lYInc
        Next j
    Next i
    
    With Worksheets(2)
        .Range("A2").Value = "X Coordinate"
        .Range("B2").Value = "Y Coordinate"
        .Range("A3").Resize(lRows * lCols, 2).Value = lNumbers
    End With
    
End Sub

 

[daveb994]

Yeah thank you so much! Helped out a lot! I'm a complete beginner and was wondering if you could possibly explain what the following line means?
lRows = Int(lX / lXInc) + 1

 

[StephenCrump]

I was guessing that you wanted:

lX(width) = 1000
lXInc (increments) = 250

i.e. for any Y value, you needed 5 rows for X, i.e. 0, 250, 500, 750, 1000

So: lRows = Int(lX / lXInc) + 1

--> lRows = Int(1000 / 250) + 1
= Int (4) + 1 = 5

In practice I expect you'll choose increments that fit exactly within the given breadth, so Int() is probably redundant.