|
New to VBA, code works but need it to offset diffrently
That code can be shortened up and forced to make sure they enter the letter you require and no numbers like this: Option Explicit Sub calctest() Dim number1 As Double, number2 As Double, answer As Double, RPM As Double, DIA As Double Dim SFM As Integer, MAX As Integer, ALPHA As String Dim NR As Long SFM = InputBox("What is the SFM?") Nxt: ALPHA = InputBox("What Alpha symbol are you using?" & vbLf & "Either: A, B, C, D, E, F, G, H, I, J") If IsNumeric(ALPHA) Then GoTo Mg 'On Error Resume Next Select Case LCase(ALPHA) Case Is = "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", _ "u", "v", "w", "x", "y", "z" Mg: MsgBox "only use one of the letters provided", vbOKOnly, "Invalid Selection" GoTo Nxt End Select MAX = InputBox("What is maximum RPM for machine?") NR = 23 DIA = Sheets("Feeds and Diameters").Range("B" & NR).Value Sheets("Feeds and Diameters").Select Range(LCase(ALPHA) & "23").Select Do Until Selection.Offset(0, -2).Value = "" RPM = SFM * 3.82 / DIA If RPM MAX Then RPM = MAX Else RPM = RPM End If number1 = Selection.Value number2 = Round(RPM, 0) answer = number1 * number2 Selection.Offset(0, 13).Value = Round(answer, 1) Selection.Offset(1, 0).Select NR = NR + 1 DIA = Sheets("Feeds and Diameters").Range("B" & NR).Value Loop End Sub Dustin S.;295474 Wrote: Code works no errors but doesn't use my RPM MAX then RPM = MAX ELSE RPM = RPM. I get feed rates but they aren't using the correct RPM due to this. You are definitely on the right track. Could you explain what the changes are that you made? So I can understand for future reference. Thank You --- Automerged consecutive post before response --- Thank you to ~stanleydgromjr~ from excelforum.com he edited my code and now works are desired. No offense ment to this forum just trying to get my question answered. Code: -------------------- Option Explicit Sub calctest() Dim number1 As Double, number2 As Double, answer As Double, RPM As Double, DIA As Double Dim SFM As Integer, MAX As Integer, ALPHA As Integer Dim NR As Long SFM = InputBox("What is the SFM?") ALPHA = InputBox("What Alpha symbol are you using?") MAX = InputBox("What is maximum RPM for machine?") NR = 23 DIA = Sheets("Feeds and Diameters").Range("B" & NR).Value Sheets("Feeds and Diameters").Select If ALPHA = 1 Then ALPHA = Range("d23").Select ElseIf ALPHA = 2 Then ALPHA = Range("e23").Select ElseIf ALPHA = 3 Then ALPHA = Range("f23").Select ElseIf ALPHA = 4 Then ALPHA = Range("g23").Select ElseIf ALPHA = 5 Then ALPHA = Range("h23").Select ElseIf ALPHA = 6 Then ALPHA = Range("i23").Select ElseIf ALPHA = 7 Then ALPHA = Range("j23").Select ElseIf ALPHA = 8 Then ALPHA = Range("k23").Select End If Do Until Selection.Offset(0, -2).Value = "" RPM = SFM * 3.82 / DIA If RPM MAX Then RPM = MAX Else RPM = RPM End If number1 = Selection.Value number2 = Round(RPM, 0) answer = number1 * number2 Selection.Offset(0, 13).Value = Round(answer, 1) Selection.Offset(1, 0).Select NR = NR + 1 DIA = Sheets("Feeds and Diameters").Range("B" & NR).Value Loop End Sub -------------------- Thanks anyway forum -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=82547 |
New to VBA, code works but need it to offset diffrently
Don thats nice! :)Don Guillett;321185 Wrote: Optional idea using Asc Sub CaseforRangeofLetters() 'k-z Select Case Asc(UCase(Range("a10"))) Case 75 To 90: x = 1 Case Else: x = "no" End Select MsgBox x End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Simon Lloyd" wrote in message ... That code can be shortened up and forced to make sure they enter the letter you require and no numbers like this: Option Explicit Sub calctest() Dim number1 As Double, number2 As Double, answer As Double, RPM As Double, DIA As Double Dim SFM As Integer, MAX As Integer, ALPHA As String Dim NR As Long SFM = InputBox("What is the SFM?") Nxt: ALPHA = InputBox("What Alpha symbol are you using?" & vbLf & "Either: A, B, C, D, E, F, G, H, I, J") If IsNumeric(ALPHA) Then GoTo Mg 'On Error Resume Next Select Case LCase(ALPHA) Case Is = "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", _ "u", "v", "w", "x", "y", "z" Mg: MsgBox "only use one of the letters provided", vbOKOnly, "Invalid Selection" GoTo Nxt End Select MAX = InputBox("What is maximum RPM for machine?") NR = 23 DIA = Sheets("Feeds and Diameters").Range("B" & NR).Value Sheets("Feeds and Diameters").Select Range(LCase(ALPHA) & "23").Select Do Until Selection.Offset(0, -2).Value = "" RPM = SFM * 3.82 / DIA If RPM MAX Then RPM = MAX Else RPM = RPM End If number1 = Selection.Value number2 = Round(RPM, 0) answer = number1 * number2 Selection.Offset(0, 13).Value = Round(answer, 1) Selection.Offset(1, 0).Select NR = NR + 1 DIA = Sheets("Feeds and Diameters").Range("B" & NR).Value Loop End Sub Dustin S.;295474 Wrote: Code works no errors but doesn't use my RPM MAX then RPM = MAX ELSE RPM = RPM. I get feed rates but they aren't using the correct RPM due to this. You are definitely on the right track. Could you explain what the changes are that you made? So I can understand for future reference. Thank You --- Automerged consecutive post before response --- Thank you to ~stanleydgromjr~ from excelforum.com he edited my code and now works are desired. No offense ment to this forum just trying to get my question answered. Code: -------------------- Option Explicit Sub calctest() Dim number1 As Double, number2 As Double, answer As Double, RPM As Double, DIA As Double Dim SFM As Integer, MAX As Integer, ALPHA As Integer Dim NR As Long SFM = InputBox("What is the SFM?") ALPHA = InputBox("What Alpha symbol are you using?") MAX = InputBox("What is maximum RPM for machine?") NR = 23 DIA = Sheets("Feeds and Diameters").Range("B" & NR).Value Sheets("Feeds and Diameters").Select If ALPHA = 1 Then ALPHA = Range("d23").Select ElseIf ALPHA = 2 Then ALPHA = Range("e23").Select ElseIf ALPHA = 3 Then ALPHA = Range("f23").Select ElseIf ALPHA = 4 Then ALPHA = Range("g23").Select ElseIf ALPHA = 5 Then ALPHA = Range("h23").Select ElseIf ALPHA = 6 Then ALPHA = Range("i23").Select ElseIf ALPHA = 7 Then ALPHA = Range("j23").Select ElseIf ALPHA = 8 Then ALPHA = Range("k23").Select End If Do Until Selection.Offset(0, -2).Value = "" RPM = SFM * 3.82 / DIA If RPM MAX Then RPM = MAX Else RPM = RPM End If number1 = Selection.Value number2 = Round(RPM, 0) answer = number1 * number2 Selection.Offset(0, 13).Value = Round(answer, 1) Selection.Offset(1, 0).Select NR = NR + 1 DIA = Sheets("Feeds and Diameters").Range("B" & NR).Value Loop End Sub -------------------- Thanks anyway forum -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'New to VBA, code works but need it to offset diffrently - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=82547) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=82547 |
New to VBA, code works but need it to offset diffrently
Thank you. -- Don Guillett Microsoft MVP Excel SalesAid Software "Simon Lloyd" wrote in message ... Don thats nice! :)Don Guillett;321185 Wrote: Optional idea using Asc Sub CaseforRangeofLetters() 'k-z Select Case Asc(UCase(Range("a10"))) Case 75 To 90: x = 1 Case Else: x = "no" End Select MsgBox x End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Simon Lloyd" wrote in message ... That code can be shortened up and forced to make sure they enter the letter you require and no numbers like this: Option Explicit Sub calctest() Dim number1 As Double, number2 As Double, answer As Double, RPM As Double, DIA As Double Dim SFM As Integer, MAX As Integer, ALPHA As String Dim NR As Long SFM = InputBox("What is the SFM?") Nxt: ALPHA = InputBox("What Alpha symbol are you using?" & vbLf & "Either: A, B, C, D, E, F, G, H, I, J") If IsNumeric(ALPHA) Then GoTo Mg 'On Error Resume Next Select Case LCase(ALPHA) Case Is = "k", "l", "m", "n", "o", "p", "q", "r", "s", "t", _ "u", "v", "w", "x", "y", "z" Mg: MsgBox "only use one of the letters provided", vbOKOnly, "Invalid Selection" GoTo Nxt End Select MAX = InputBox("What is maximum RPM for machine?") NR = 23 DIA = Sheets("Feeds and Diameters").Range("B" & NR).Value Sheets("Feeds and Diameters").Select Range(LCase(ALPHA) & "23").Select Do Until Selection.Offset(0, -2).Value = "" RPM = SFM * 3.82 / DIA If RPM MAX Then RPM = MAX Else RPM = RPM End If number1 = Selection.Value number2 = Round(RPM, 0) answer = number1 * number2 Selection.Offset(0, 13).Value = Round(answer, 1) Selection.Offset(1, 0).Select NR = NR + 1 DIA = Sheets("Feeds and Diameters").Range("B" & NR).Value Loop End Sub Dustin S.;295474 Wrote: Code works no errors but doesn't use my RPM MAX then RPM = MAX ELSE RPM = RPM. I get feed rates but they aren't using the correct RPM due to this. You are definitely on the right track. Could you explain what the changes are that you made? So I can understand for future reference. Thank You --- Automerged consecutive post before response --- Thank you to ~stanleydgromjr~ from excelforum.com he edited my code and now works are desired. No offense ment to this forum just trying to get my question answered. Code: -------------------- Option Explicit Sub calctest() Dim number1 As Double, number2 As Double, answer As Double, RPM As Double, DIA As Double Dim SFM As Integer, MAX As Integer, ALPHA As Integer Dim NR As Long SFM = InputBox("What is the SFM?") ALPHA = InputBox("What Alpha symbol are you using?") MAX = InputBox("What is maximum RPM for machine?") NR = 23 DIA = Sheets("Feeds and Diameters").Range("B" & NR).Value Sheets("Feeds and Diameters").Select If ALPHA = 1 Then ALPHA = Range("d23").Select ElseIf ALPHA = 2 Then ALPHA = Range("e23").Select ElseIf ALPHA = 3 Then ALPHA = Range("f23").Select ElseIf ALPHA = 4 Then ALPHA = Range("g23").Select ElseIf ALPHA = 5 Then ALPHA = Range("h23").Select ElseIf ALPHA = 6 Then ALPHA = Range("i23").Select ElseIf ALPHA = 7 Then ALPHA = Range("j23").Select ElseIf ALPHA = 8 Then ALPHA = Range("k23").Select End If Do Until Selection.Offset(0, -2).Value = "" RPM = SFM * 3.82 / DIA If RPM MAX Then RPM = MAX Else RPM = RPM End If number1 = Selection.Value number2 = Round(RPM, 0) answer = number1 * number2 Selection.Offset(0, 13).Value = Round(answer, 1) Selection.Offset(1, 0).Select NR = NR + 1 DIA = Sheets("Feeds and Diameters").Range("B" & NR).Value Loop End Sub -------------------- Thanks anyway forum -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' ('The Code Cage' (http://www.thecodecage.com)) ------------------------------------------------------------------------ Simon Lloyd's Profile: 'The Code Cage Forums - View Profile: Simon Lloyd' (http://www.thecodecage.com/forumz/member.php?userid=1) View this thread: 'New to VBA, code works but need it to offset diffrently - The Code Cage Forums' (http://www.thecodecage.com/forumz/sh...ad.php?t=82547) -- Simon Lloyd Regards, Simon Lloyd 'The Code Cage' (http://www.thecodecage.com) ------------------------------------------------------------------------ Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1 View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=82547 |
| All times are GMT +1. The time now is 05:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com