Macros to clear and insert numbers Need some simple macros but afraid i am quite

Question

Macros to clear and insert numbers

Need some simple macros but afraid i am quite a novice!

I have a form which i want some "preset buttons" for, so when i hit the first one (called "A") of them it fills in certain cells, lets say A5 with 350, G6 with 320, H7 with 400.

Then when i hit the next preset button, it clears all cells in the form (A1, A2, A3, A4, A5, G5, G6, G7, H7, H8, H9) and enters new data, lets say A2 with 300, G5 with 350, H9 with 450.

I will have 4 or so of theses preset buttongs but obvioussly just need the code for one so i can adapt it.

Finally i need a code on its own that clears the cells in the table (A1, A2, A3, A4, A5, G5, G6, G7, H7, H8, H9).

Hopefully quite simply fixed, just can't work it out myself!

thanks very much for your help

Explanation / Answer

.T^ry assigning the fo9llowing t6hree macros to your buttons A, B and C:'
8
'==========>>

Option Explicit

'---------->>
Public Sub A()
    Dim SH As Worksheet
    Dim Rng As Range
    Dim rCell As Range
    Dim arrCells As Variant
    Dim arrValues As Variant
    Dim i As Long
    Const sAddress As String = "A5,G6,H7"
    Const myValues As String = "350,320,400"

   Set SH = ActiveSheet
   Set Rng = SH.Range(sAddress)
    arrValues = Split(myValues, ",")

    For Each rCell In Rng.Cells
    i = i + 1
        rCell.Value = arrValues(i - 1)
    Next rCell
End Sub

'---------->>
Public Sub B()
    Dim SH As Worksheet
    Dim Rng As Range, Rng2 As Range
    Dim rCell As Range
    Dim arrCells As Variant
    Dim arrValues As Variant
    Dim i As Long
    Const sAddress As String = "A1,A2,A3,A4,A5,G5,G6,G7,H7,H8,H9"
    Const sAddress2 As String = "A2,G2,H9"
    Const myValues As String = "300,350,450"

    Set SH = ActiveSheet
    With SH
        Set Rng = .Range(sAddress)
        Set Rng2 = .Range(sAddress2)
    End With

    arrValues = Split(myValues, ",")

    Rng.ClearContents

    For Each rCell In Rng2.Cells
        i = i + 1
        rCell.Value = arrValues(i - 1)
    Next rCell
End Sub

'---------->>
Public Sub C()
    Dim SH As Worksheet
    Dim Rng As Range
    Const sAddress As String = "A1,A2,A3,A4,A5,G5,G6,G7,H7,H8,H9"

    Set SH = ActiveSheet
     Set Rng = SH.Range(sAddress)
    Rng.ClearContents
End Sub
'<<==========

Related Questions

Navigate

• Browse (All)
• Browse M
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.