Good Day,
I have a spin button in my excel sheet. I have it to run through one range of numbers. Now I need some help on how to get it to populate the remaining fields. I have it putting the inches in B2. Then I need the m, cm and mm to fill in B3, B4 and B5 automatically.
A B
1 in 19
2 m 0.2286
3 cm 22.86
4 mm 228.600
The data is located in cells B, C, D, E from 8 - 47.
Example:
in m cm mm
9 0.2286 22.86 228.600
10 0.254 25.40 254.000
11 0.2794 27.94 279.400
....
48 1.2192 121.92 1219.200
Here is my code so far:
Private Sub SpinButton1_SpinDown()
DVDown
End Sub
Private Sub SpinButton1_SpinUp()
DVUp
End Sub
Module:
Sub DVUp()
Dim intDV As Integer
Dim ws As Worksheet
Dim c As Range
Dim rngList As Range
Set ws = Sheets("DVScroll")
Set c = ws.Range("B2")
Set rngList = ws.Range("in")
intDV = 0
On Error Resume Next
intDV = Application.WorksheetFunction.Match(c.Value, rngList, 0)
If intDV = 0 Or intDV = rngList.Rows.Count Then
c.Value = rngList.Cells(1, 1)
Else
c.Value = rngList.Cells(intDV + 1, 1)
End If
End Sub
Sub DVDown()
Dim intDV As Integer
Dim ws As Worksheet
Dim c As Range
Dim rngList As Range
Set ws = Sheets("DVScroll")
Set c = ws.Range("B2")
Set rngList = ws.Range("in")
intDV = 0
On Error Resume Next
intDV = Application.WorksheetFunction.Match(c.Value, rngList, 0)
If intDV = 0 Or intDV = 1 Then
c.Value = rngList.Cells(rngList.Rows.Count, 1)
Else
c.Value = rngList.Cells(intDV - 1, 1)
End If
End Sub
Please help.
thanks in advance
Jennifer
I have a spin button in my excel sheet. I have it to run through one range of numbers. Now I need some help on how to get it to populate the remaining fields. I have it putting the inches in B2. Then I need the m, cm and mm to fill in B3, B4 and B5 automatically.
A B
1 in 19
2 m 0.2286
3 cm 22.86
4 mm 228.600
The data is located in cells B, C, D, E from 8 - 47.
Example:
in m cm mm
9 0.2286 22.86 228.600
10 0.254 25.40 254.000
11 0.2794 27.94 279.400
....
48 1.2192 121.92 1219.200
Here is my code so far:
Private Sub SpinButton1_SpinDown()
DVDown
End Sub
Private Sub SpinButton1_SpinUp()
DVUp
End Sub
Module:
Sub DVUp()
Dim intDV As Integer
Dim ws As Worksheet
Dim c As Range
Dim rngList As Range
Set ws = Sheets("DVScroll")
Set c = ws.Range("B2")
Set rngList = ws.Range("in")
intDV = 0
On Error Resume Next
intDV = Application.WorksheetFunction.Match(c.Value, rngList, 0)
If intDV = 0 Or intDV = rngList.Rows.Count Then
c.Value = rngList.Cells(1, 1)
Else
c.Value = rngList.Cells(intDV + 1, 1)
End If
End Sub
Sub DVDown()
Dim intDV As Integer
Dim ws As Worksheet
Dim c As Range
Dim rngList As Range
Set ws = Sheets("DVScroll")
Set c = ws.Range("B2")
Set rngList = ws.Range("in")
intDV = 0
On Error Resume Next
intDV = Application.WorksheetFunction.Match(c.Value, rngList, 0)
If intDV = 0 Or intDV = 1 Then
c.Value = rngList.Cells(rngList.Rows.Count, 1)
Else
c.Value = rngList.Cells(intDV - 1, 1)
End If
End Sub
Please help.
thanks in advance
Jennifer