Consulting

Results 1 to 4 of 4

Thread: Solved: Getting rid of message box

  1. #1
    VBAX Expert
    Joined
    May 2006
    Location
    Oklahoma City, OK
    Posts
    532
    Location

    Solved: Getting rid of message box

    In the following macro all of the cells "G1,D3,D5,D7,D9,D11,D13" have to have data in them otherwise they won't copy to database. I can't figure out how to get rid of the requirement to have data in the cells so that they will copy to the database even if they are empty.

    Modules: modData-


    [VBA] Sub UpdateLogWorksheet()
    Dim historyWks As Worksheet
    Dim inputWks As Worksheet
    Dim nextRow As Long
    Dim oCol As Long
    Dim myRng As Range
    Dim myCopy As String
    Dim myCell As Range

    'cells to copy from Input sheet - some contain formulas
    myCopy = "G1,D3,D5,D7,D9,D11,D13"
    Set inputWks = Worksheets("Input")
    Set historyWks = Worksheets("Database")
    With historyWks
    nextRow = .Cells(.Rows.Count, "H").End(xlUp).Offset(1, 0).Row
    End With
    With inputWks
    Set myRng = .Range(myCopy)
    If Application.CountA(myRng) <> myRng.Cells.Count Then
    MsgBox "Please fill in all the cells!"
    Exit Sub
    End If
    End With
    With historyWks
    With .Cells(nextRow, "H")
    .Value = Now
    .NumberFormat = "mm/dd/yyyy hh:mm:ss"
    End With
    .Cells(nextRow, "A").Value = Application.UserName
    oCol = 1
    For Each myCell In myRng.Cells
    historyWks.Cells(nextRow, oCol).Value = myCell.Value
    oCol = oCol + 1
    Next myCell
    End With

    'clear input cells that contain constants
    With inputWks
    On Error Resume Next
    With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
    .ClearContents
    Application.Goto .Cells(1) ', Scroll:=True
    End With
    On Error GoTo 0
    End With
    End Sub[/VBA]


    Best regards,

    Charlie

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    [vba]

    Option Explicit

    Sub UpdateLogWorksheet()
    Dim historyWks As Worksheet
    Dim inputWks As Worksheet
    Dim nextRow As Long
    Dim oCol As Long
    Dim myRng As Range
    Dim myCopy As String
    Dim myCell As Range

    'cells to copy from Input sheet - some contain formulas
    myCopy = "G1,D3,D5,D7,D9,D11,D13"
    Set inputWks = Worksheets("Input")
    Set historyWks = Worksheets("Database")
    With historyWks
    nextRow = .Cells(.Rows.Count, "H").End(xlUp).Offset(1, 0).Row
    End With
    With inputWks
    Set myRng = .Range(myCopy)
    End With
    With historyWks
    With .Cells(nextRow, "H")
    .Value = Now
    .NumberFormat = "mm/dd/yyyy hh:mm:ss"
    End With
    .Cells(nextRow, "A").Value = Application.UserName
    oCol = 1
    For Each myCell In myRng.Cells
    historyWks.Cells(nextRow, oCol).Value = myCell.Value
    oCol = oCol + 1
    Next myCell
    End With

    'clear input cells that contain constants
    With inputWks
    On Error Resume Next
    With .Range(myCopy).Cells.SpecialCells(xlCellTypeConstants)
    .ClearContents
    Application.Goto .Cells(1) ', Scroll:=True
    End With
    On Error GoTo 0
    End With
    End Sub
    [/vba]

  3. #3
    VBAX Expert
    Joined
    May 2006
    Location
    Oklahoma City, OK
    Posts
    532
    Location
    Thanks XLD for your response.....I was forgetting to take out the "End If". You ever get that when you look so hard that "you can't see the trees throught the forest"

    Thanks for you help,

    Charlie

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,264
    Location
    Often ... too often

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •