Snasui Excel Forum

Code: Select all

Sub PreviewData()
Dim myRange As Range
Dim r As Range
Dim i As Integer
Dim j As Long
Dim l As Range
Set l = Worksheets("Input").Range("G10")

j = Worksheets("Database").Range("A1:BB1").Columns.Count

If l = 0 Then
    MsgBox "Not found data. Try again."
    Exit Sub
End If
    
Set myRange = Worksheets("Input").Range("C9, C10, C11, C12, C14, G14, L14, C15," & _
    "G15, L15, C16, L16, D17, F20, C21, C24,F24, I24, A29, A40, A47, C51, E51," & _
    "J66, A55, C55, D55, G55, J55, A57, C57,D57, G57, J57, A59, C59, D59, G59, J59, A61, C61, D61, G61, J61, A63, C63, D63, G63, J63, A65, C65, D65")
For Each r In myRange
    i = i + 1
    r = Worksheets("Database").Cells(l + 2, i)
Next r
MsgBox "Finish"
Range("C4").Locked = True
 End Sub

Code: Select all

Set myRange = Worksheets("Input").Range("C9, C10, C11, C12, C14, G14, L14, C15," & _
    "G15, L15, C16, L16, D17, F20, C21, C24,F24, I24, A29, A40, A47, C51, E51," & _
    "J66, A55, C55, D55, G55, J55, A57, C57,D57, G57, J57, A59, C59, D59, G59, J59, A61, C61, D61, G61, J61, A63, C63, D63, G63, J63, A65, C65, D65")

Code: Select all

("C9", "C10", "C11", _
        "C12", "C14", "G14", "L14", "C15", "G15", _
        "L15", "C16", "G16", "L16", "D17", "J17", "C18", "E18", "G18", "I18", "K18", "C19", "G19", "C20", "F20", "J20", "L20", _
        "C21", "C24", "F24", "I24", "C25", "H25", "D26", "G26", "L26", "A29", _
        "A40", "A47", "C51", "E51", "H51", "C66", "j66", _
        "A55", "C55", "D55", "G55", "j55", _
        "A57", "C57", "D57", "G57", "j57", _
        "A59", "C59", "D59", "G59", "j59", _
        "A61", "C61", "D61", "G61", "j61", _
        "A63", "C63", "D63", "G63", "j63", _
        "A65", "C65", "D65", "G65", "j65")

Code: Select all

Sub PreviewData()
    Dim myRange As Variant
    Dim i As Integer
    Dim j As Long
    Dim l As Range
    Dim k As Integer
    Set l = Worksheets("Form").Range("A1")
    
    j = Worksheets("Data").Range("A1:AV1").Columns.Count
    
    If l = 0 Then
        MsgBox "Not found data. Try again."
        Exit Sub
    End If
    With Sheets("Form")
        myRange = Array("C9", "C10", "C11", "C12", "C14", "G14", "L14", "C15", "G15", "L15", "C16", _
            "L16", "D17", "F20", "C21", "C24", "F24", "I24", "A29", "A40", "A47", "C51", "E51", _
            "J66", "A55", "C55", "D55", "G55", "j55", "A57", "C57", "D57", "G57", "j57", "A59", "C59", "D59", "G59", _
            "j59", "A61", "C61", "D61", "G61", "j61", "A63", "C63", "D63", "G63", "j63", "A65", "C65", "D65", "G65", "j65")
            For k = 0 To UBound(myRange)
                i = i + 1
                .Range(myRange(k)).Value = Worksheets("Data").Cells(l + 2, i)
            Next k
    End With
    MsgBox "Finish"
    Range("C4").Locked = True
End Sub

Code: Select all

Sub ReplaceData()
Dim rs As Range
Dim rt As Range
Set rs = Worksheets("Input").Range("A1")
Set rt = Worksheets("Database").Range("A3").Offset
    Worksheets("Input").Range("A1"), 0)
If Worksheets("Input").Range("A3") = "" Then
    MsgBox "Can't replace data"
    Exit Sub
End If
rs.Copy: rt.PasteSpecial xlPasteValues
Application.CutCopyMode = False
MsgBox "Replace Complete"
End Sub
Application.CutCopyMode = False
MsgBox "Replace Complete"
End Sub

Code: Select all

Sub rdata()
Dim rs As Range
Dim rt As Range
Set rs = Worksheets("Temp1").Range("A4:CA4")
Set rt = Worksheets("Database").Range("A2").Offset( _
    Worksheets("Temp1").Range("CB2"), 0)
If Worksheets("Temp1").Range("A4") = "" Then
    MsgBox "Can't replace data"
    Exit Sub
End If
rs.Copy: rt.PasteSpecial xlPasteValues
Application.CutCopyMode = False
MsgBox "Replace Complete"
End Sub