I am new to writing VBA code (now using Excel 2013 but started writing it in 2010) and have gotten some help along the way to get to where I am, but I am a bit lost again. In essence what I am trying to do is get a range of data (CNC Machine G-Code) to repeat a specified number of times and allow a user to define a cell within that range that will have a formula apply to it. That formula needs to increment each time the range is copied down.
I've designed a user form with the following inputs:
To establish the range - Starts Cell & End cell
To establish the number of times the range will be copied and pasted - Number of Operations
To establish the cell where the formula needs to be applied - A-Axis Variable Range (Cell)
To Set the amount by which the formula needs to increment - AAxis Variable
The idea behind this is that the range of data can contain many different things and may need to be repeated any different number of times depending on the unique need, but that the formula will always need to be applied somewhere in that range of data and that the user will need to point to that cell to make it happen.
I have two things I need some help with. One, I now get an error windows that pop up in 2013 that I didn't used to see in 2010.
"You have not specified range values" comes up once when I fill out the user form and click Generate Code, but the operation works with the exception of what I am trying to do with the formula.
The second is the big one. I cannot seem to figure out the correct syntax to make the formula work.
Here is the code:
Any help would be greatly appreciated.
I've designed a user form with the following inputs:
To establish the range - Starts Cell & End cell
To establish the number of times the range will be copied and pasted - Number of Operations
To establish the cell where the formula needs to be applied - A-Axis Variable Range (Cell)
To Set the amount by which the formula needs to increment - AAxis Variable
The idea behind this is that the range of data can contain many different things and may need to be repeated any different number of times depending on the unique need, but that the formula will always need to be applied somewhere in that range of data and that the user will need to point to that cell to make it happen.
I have two things I need some help with. One, I now get an error windows that pop up in 2013 that I didn't used to see in 2010.
"You have not specified range values" comes up once when I fill out the user form and click Generate Code, but the operation works with the exception of what I am trying to do with the formula.
The second is the big one. I cannot seem to figure out the correct syntax to make the formula work.
Here is the code:
Code:
Private Sub AAxisCell_Change()
End Sub
Private Sub AAxisIncrement_Change()
End Sub
Private Sub Label4_Click()
End Sub
Private Sub Label7_Click()
End Sub
Private Sub NumberofOps_Change()
End Sub
Private Sub RowOffset_Change()
End Sub
Private Sub UserForm_Initialize()
' Inititalize is what you use to start your code action when opening the form.
' This routing will empty all of the text boxes when the form is opened.
' "Private Sub" means this routine is only accessible to this specific module.
'Empty RangeStart Text Box
RangeStart.Value = ""
'Empty RangeEnd Text Box
RangeEnd.Value = ""
'Empty A-Axis Variable Row
AAxisCell.Value = ""
'Empty A-Axis Increment Variable
AAxisIncrement.Value = ""
'Set Focus on Range Row upon form open
RangeStart.SetFocus
End Sub
Private Sub GenerateCodeButton_Click()
On Error GoTo WrongValue
Dim i As Integer
Dim Code As Range
Dim AAxisCell As Range
Dim AAxisIncrement As Integer
Set Code = Range(RangeStart.Value & ":" & RangeEnd.Value)
Set AAxisCell = Range("AxisCell").Value = "=(360 - (i * AAxisIncrement.Value)"
For i = 1 To CLng(NumberofOps.Value)
If NumberofOps.Value < 1 Then
MsgBox "Number of Operations must be greater than or equal to 1"
Resume Next
End If
Code.Copy Range("A" & Rows.Count).End(3)(2)
Next i
Exit Sub
WrongValue:
If Err.Number = 1004 Then
MsgBox "You have not specified valid range values"
Resume Next
End If
End Sub
Private Sub ClearButton_Click()
Call UserForm_Initialize
End Sub
Private Sub CancelButton_Click()
Unload Me
End Sub
Private Sub HideMenuButton_Click()
Me.Hide
End Sub
Any help would be greatly appreciated.