Snasui Excel Forum

Code: Select all

For Each ws In Sheets
    For Each r In ws.Range("a2", ws.Range("a" & Rows.Count).End(xlUp))
        If r <> "" Then r.Value = Val(r.Value)
    Next r
Next ws

Code: Select all

Sub ConvertAllShts()
For Each WS In Sheets
For Each r In WS.UsedRange
If r <> "" Then r.Value = Val(r.Value)
Next
Next
End Sub

Code: Select all

Private Sub TextBox1_Change()
        Worksheets("temp").Range("A2") = Me.TextBox1
End Sub

Private Sub TextBox2_Change()
        Worksheets("temp").Range("B2") = Me.TextBox2
End Sub

Private Sub TextBox3_Change()
        Worksheets("temp").Range("C2") = Me.TextBox3
End Sub
Private Sub ComboBox19_Change()
        Worksheets("temp").Range("D2") = Me.ComboBox19
End Sub

Private Sub ComboBox1_Change()
        Worksheets("temp").Range("E2") = Me.ComboBox1
End Sub

Private Sub ComboBox2_Change()
        Worksheets("temp").Range("F2") = Me.ComboBox2
End Sub

Private Sub ComboBox3_Change()
        Worksheets("temp").Range("G2") = Me.ComboBox3
End Sub

Private Sub ComboBox5_Change()
        Worksheets("temp").Range("H2") = Me.ComboBox5
End Sub
Private Sub ComboBox6_Change()
        Worksheets("temp").Range("I2") = Me.ComboBox6
End Sub
Private Sub ComboBox8_Change()
        Worksheets("temp").Range("J2") = Me.ComboBox8
End Sub
Private Sub ComboBox9_Change()
        Worksheets("temp").Range("K2") = Me.ComboBox9
End Sub
Private Sub ComboBox10_Change()
        Worksheets("temp").Range("L2") = Me.ComboBox10
End Sub
Private Sub ComboBox11_Change()
        Worksheets("temp").Range("M2") = Me.ComboBox11
End Sub
Private Sub ComboBox7_Change()
        Worksheets("temp").Range("N2") = Me.ComboBox7
End Sub
Private Sub ComboBox14_Change()
        Worksheets("temp").Range("O2") = Me.ComboBox14
End Sub
Private Sub ComboBox15_Change()
        Worksheets("temp").Range("P2") = Me.ComboBox15
End Sub
Private Sub ComboBox16_Change()
        Worksheets("temp").Range("Q2") = Me.ComboBox16
End Sub
Private Sub ComboBox17_Change()
        Worksheets("temp").Range("R2") = Me.ComboBox17
End Sub
Private Sub ComboBox13_Change()
        Worksheets("temp").Range("S2") = Me.ComboBox13
End Sub

Private Sub CommandButton1_Click()
    Dim range2014 As Range
    Dim range2015 As Range
    Dim range2016 As Range
    Dim r As Range
    With Sheets("temp")
        Set range2014 = .Range("E2:I2")
        Set range2015 = .Range("J2:N2")
        Set range2016 = .Range("O2:S2")
        For Each r In range2014
            If r = "Done" Then r.Offset(0, 5) = r
        Next r
        For Each r In range2015
            If r = "Done" Then r.Offset(0, 5) = r
        Next r
        For Each r In range2016
            If r = "Done" Then r.Offset(0, 5) = r
        Next r
        .Range("A2:S2").Resize(1, 19).Copy
        Sheets("Sheet1").Range("A" & Rows.Count) _
            .End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        MsgBox ("Already Saved")
        .Range("A2:S2").ClearContents
    End With
End Sub

Code: Select all

Private Sub CommandButton1_Click()
    Dim range2014 As Range
    Dim range2015 As Range
    Dim range2016 As Range
    Dim r As Range
    With Sheets("temp")
        Set range2014 = .Range("E2:I2")
        Set range2015 = .Range("J2:N2")
        Set range2016 = .Range("O2:S2")
        For Each r In range2014
            If r = "Done" Then r.Offset(0, 5) = r
        Next r
        For Each r In range2015
            If r = "Done" Then r.Offset(0, 5) = r
        Next r
        For Each r In range2016
            If r = "Done" Then r.Offset(0, 5) = r
        Next r
        .Range("A2:S2").Resize(1, 19).Copy
        Sheets("Sheet1").Range("A" & Rows.Count) _
            .End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        MsgBox ("Already Saved")
        .Range("A2:S2").ClearContents
    End With
End Sub

Code: Select all

Private Sub CommandButton1_Click()
    Dim range2014 As Range
    Dim range2015 As Range
    Dim r As Range
    With Sheets("temp")
        Set range2014 = .Range("e2:i2")
        Set range2015 = .Range("j2:n2")
        For Each r In range2014
            If r = "Done" Then r.Offset(0, 5) = r
        Next r
        For Each r In range2015
            If r = "Done" Then r.Offset(0, 5) = r
        Next r
        .Range("A2:S2").Resize(1, 19).Copy
        Sheets("Sheet1").Range("A" & Rows.Count) _
            .End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
        MsgBox ("Already Saved")
        .Range("A2:S2").ClearContents
    End With
End Sub

Code: Select all

Private Sub ComboBox8_Change()
        Dim ws As Worksheet
        Set ws = Worksheets("temp")
        If ws.Range("E2") = "Done" Then
            ws.Range("J2") = "Done"
        Else
            ws.Range("J2") = Me.ComboBox8
        End If
        Set ws = Nothing
End Sub
Private Sub ComboBox9_Change()
        Dim ws As Worksheet
        Set ws = Worksheets("temp")
        If ws.Range("F2") = "Done" Then
            ws.Range("K2") = "Done"
        Else
            ws.Range("K2") = Me.ComboBox9
        End If
        Set ws = Nothing
End Sub

Private Sub ComboBox10_Change()
        Dim ws As Worksheet
        Set ws = Worksheets("temp")
        If ws.Range("G2") = "Done" Then
            ws.Range("L2") = "Done"
        Else
            ws.Range("L2") = Me.ComboBox10
        End If
        Set ws = Nothing
End Sub
Private Sub ComboBox11_Change()
        Dim ws As Worksheet
        Set ws = Worksheets("temp")
        If ws.Range("H2") = "Done" Then
            ws.Range("M2") = "Done"
        Else
            ws.Range("M2") = Me.ComboBox11
        End If
        Set ws = Nothing
End Sub
Private Sub ComboBox7_Change()
        Dim ws As Worksheet
        Set ws = Worksheets("temp")
        If ws.Range("I2") = "Done" Then
            ws.Range("N2") = "Done"
        Else
            ws.Range("N2") = Me.ComboBox7
        End If
        Set ws = Nothing
End Sub
Private Sub ComboBox14_Change()
        Dim ws As Worksheet
        Set ws = Worksheets("temp")
        If ws.Range("J2") = "Done" Then
            ws.Range("O2") = "Done"
        Else
            ws.Range("O2") = Me.ComboBox14
        End If
        Set ws = Nothing
End Sub
Private Sub ComboBox15_Change()
        Dim ws As Worksheet
        Set ws = Worksheets("temp")
        If ws.Range("K2") = "Done" Then
            ws.Range("P2") = "Done"
        Else
            ws.Range("P2") = Me.ComboBox15
        End If
        Set ws = Nothing
End Sub
Private Sub ComboBox16_Change()
        Dim ws As Worksheet
        Set ws = Worksheets("temp")
        If ws.Range("L2") = "Done" Then
            ws.Range("Q2") = "Done"
        Else
            ws.Range("Q2") = Me.ComboBox16
        End If
        Set ws = Nothing
End Sub
Private Sub ComboBox17_Change()
        Dim ws As Worksheet
        Set ws = Worksheets("temp")
        If ws.Range("M2") = "Done" Then
            ws.Range("R2") = "Done"
        Else
            ws.Range("R2") = Me.ComboBox17
        End If
        Set ws = Nothing
End Sub
Private Sub ComboBox13_Change()
        Dim ws As Worksheet
        Set ws = Worksheets("temp")
        If ws.Range("N2") = "Done" Then
            ws.Range("S2") = "Done"
        Else
            ws.Range("S2") = Me.ComboBox13
        End If
        Set ws = Nothing
End Sub