Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > New to VBA, code works but need it to offset diffrently
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,124
Default New to VBA, code works but need it to offset diffrently
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' (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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,124
Default 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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
ShowMe function works fine except I need the offset to adjustdepending on column [email protected] Excel Programming 2 November 25th 08 07:09 PM
Code somewhat works. Please help? jsc3489 Excel Programming 4 December 2nd 05 07:51 PM
Offset Function works in cell, not in named range DragonslayerApps Excel Worksheet Functions 0 July 25th 05 04:39 PM
Code works for any WBK except the PMW Mark Tangard[_3_] Excel Programming 3 July 17th 05 11:54 AM
Why won't this code works ksnapp[_37_] Excel Programming 6 April 1st 04 01:44 PM


All times are GMT +1. The time now is 12:22 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement