Check if cells are empty before pasting values

[ipbr21054]

Morning,
Im having an issue with the code below & now confused myself.

Previously i had just copied / pasted the values.
I have now see nothing is in place should i paste over value already in the cells as shown.
I have added the If IsEmpty code to check if cells have values & if Yes then just show msgbox but if empty then allow the paste of values to contine.
This is where ive now confused myself.

Private Sub MayButton_Click()
If IsEmpty(Range"(E4:E13").Value)= True Then
MsgBox "CELLS HAVE VALUES OVERWRITE ?", VbYesNo"
If answer = vbYes Then
  MsgBox "Yes"
Else
  MsgBox "Calls Have Values"
Exit Sub
End If

Range("I9:I18").Copy Destination:=Range("E4:E13")
MsgBox "ALL FIGURES HAVE BEEN TRANSFERED", vbInformation, "MONTHS FIGUES MESSAGE"
Unload SUMMARYSHEETYEAR
Range("I9:I18").ClearContents
Range("I9").Select
End Sub

 

[Jimmypop]

Hi there

Do get an error with the code above?

 

[ipbr21054]

NO as i cant run it as it contains errors

 

[Jimmypop]

Ok... Not at PC at the moment but try the below...

Private Sub MayButton_Click()
    If IsEmpty(Range"(E4:E13").Value = TRUE Then
        MsgBox "CELLS HAVE VALUES OVERWRITE ?", VbYesNo
        If answer = vbYes Then
            MsgBox "Yes"
        Else
            MsgBox "Calls Have Values"
            Exit Sub
        End If
        Range("I9:I18").Copy Destination:=Range("E4:E13")
        MsgBox "ALL FIGURES HAVE BEEN TRANSFERED", vbInformation, "MONTHS FIGUES MESSAGE"
        Unload SUMMARYSHEETYEAR
        Range("I9:I18").ClearContents
        Range("I9").Select
    End Sub

The only possible problem I could see is the extra ")" at...

.Value)= True Then

and extra " at

VbYesNo"

" in wrong place

If IsEmpty(Range"(E4:E13")

However still untested.

 

[Joe4]

I see all sorts of issues and typos with this section here:

If IsEmpty(Range"(E4:E13").Value)= True Then
MsgBox "CELLS HAVE VALUES OVERWRITE ?", VbYesNo"
If answer = vbYes Then
  MsgBox "Yes"
Else
  MsgBox "Calls Have Values"
Exit Sub
End If

Try replacing that block with this:

If WorksheetFunction.CountA(Range("E4:E13")) > 0 Then
    answer = MsgBox("CELLS HAVE VALUES OVERWRITE?", vbYesNo)
    If answer = vbYes Then
        MsgBox "Yes"
    Else
        MsgBox "Cells Have Values"
        Exit Sub
    End If
End If

 

[ipbr21054]

I think this is now what i need but syntax error on line shown in Red

Private Sub MayButton_Click()
If WorksheetFunction.CountA(Range("E4:E13")) > 0 Then
    answer = MsgBox("CELLS HAVE VALUES OVERWRITE THEM ?, vbYesNo)vbCritical"
    
    If answer = vbNo Then
    Exit Sub
    Else
    
    Range("I9:I18").Copy Destination:=Range("E4:E13")
    MsgBox "ALL FIGURES HAVE BEEN TRANSFERED", vbInformation, "MONTHS FIGUES MESSAGE"
    Unload SUMMARYSHEETYEAR
    Range("I9:I18").ClearContents
    Range("I9").Select
    End If
End If

End Sub

 

[Joe4]

You have your double-quote in the wrong place (should be after the question mark), and you cannot put "vbCritical" after the parentheses.
Did you try my solution? I already corrected all the issues your code had to do what you want?

 

[ipbr21054]

I did & it sorted it out.
What i had supplied in my post was to get me going.
This is what i would like to do but still syntax error in same line.

Run the code.
Check if values are in the range shown.
If yes show msgbox with yes / no
Selecting No exits sub so i can then look at cells in question.
Selecting Yes overwrites the values.
Msgbox shown to confirm All figures transfered>

I think once syntax is done all will be ok

Private Sub MayButton_Click()
If WorksheetFunction.CountA(Range("E4:E13")) > 0 Then
    answer = MsgBox("CELLS HAVE VALUES OVERWRITE THEM ?", vbCritical + vbYesNo"
    If answer = vbNo Then
    Exit Sub
    Else
    
    Range("I9:I18").Copy Destination:=Range("E4:E13")
    MsgBox "ALL FIGURES HAVE BEEN TRANSFERED", vbInformation, "MONTHS FIGUES MESSAGE"
    Unload SUMMARYSHEETYEAR
    Range("I9:I18").ClearContents
    Range("I9").Select
    End If
End If

End Sub

 

[ipbr21054]

Think ive sorted it

Private Sub MayButton_Click()
If WorksheetFunction.CountA(Range("E4:E13")) > 0 Then
    answer = MsgBox("CELLS CONTAIN VALUES, DO WE OVERWRITE THEM ?", vbCritical + vbYesNo)
    If answer = vbNo Then
    Exit Sub
    Else
    Range("I9:I18").Copy Destination:=Range("E4:E13")
    MsgBox "ALL FIGURES HAVE BEEN TRANSFERED", vbInformation, "MONTHS FIGUES MESSAGE"
    Unload SUMMARYSHEETYEAR
    Range("I9:I18").ClearContents
    Range("I9").Select
    End If
End If

End Sub

 

[ipbr21054]

The code below is what i have at present.
It checks if values are in the cell range and does what should be done when selecting the Yes / No on the Msgbox.

What ive overlooked now is what if the cell range is empty.
Currently with the cell range is empty so i dont get to see the Msgbox & obviously no values are pasted.
I need to be able to paste values if cell range is empty THEN if cells have values use code supplied below

Private Sub MayButton_Click()
If WorksheetFunction.CountA(Range("E4:E13")) > 0 Then
    answer = MsgBox("CELLS CONTAIN VALUES ALREADY, OVERWRITE THEM ?", vbCritical + vbYesNo)
    If answer = vbNo Then
    Exit Sub
    Else
    
    Range("I9:I18").Copy Destination:=Range("E4:E13")
    MsgBox "ALL FIGURES HAVE BEEN TRANSFERED", vbInformation, "MONTHS FIGUES MESSAGE"
    Unload SUMMARYSHEETYEAR
    Range("I9:I18").ClearContents
    Range("I9").Select
    End If
End If

End Sub