VBA Find and Message Box Execution

[streub]

Below is a sub that allows users to enter a monthly sales budget. If the monthly budget has already been entered I need the message box to advise the user. If not, it copies the budget to the budget data sheet.

I am am experiencing difficulty achieving that goal.


Private Sub cmdent_Click() 'Enter monthly budget
Dim ws As Worksheet
Set ws = Worksheets("Budgets")
Dim wk As Worksheet
Set wk = Worksheets("Pref")

Dim strFind
Dim rSearch As Range 'range to search
Set rSearch = ws.Range("a:a")
Dim c

strFind = wk.Range("o13").Value

With rSearch
Set c = .Find(strFind, LookIn:=xlValues, MatchCase:=True)
If Not c Is Nothing Then 'found it

If ActiveCell.Offset(0, 1).Value = wk.Range("o14").Value Then
MsgBox "Budget Already Entered"
End If
Exit Sub

Else
Range("o13:o46").Copy
Worksheets("budgets").Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True

End If
End With
End Sub

[Rob342]

Streub
Have changed the code in red
Set c = .Find(strFind, LookIn:=xlValues, MatchCase:=True)
If Not c Is Nothing Then 'found it

If ActiveCell.Offset(0, 1).Value = wk.Range("o14").Value Then
MsgBox "Budget Already Entered"
Exit Sub
End If

[streub]

Nice to meet you Rob and thank you for the assistance. I will install, test and inform you.

Streub

[streub]

Perhaps I was not precise. Using "c" , I need to also determine if the next cell to the right equals wk.range("o14").value. If so, then exit the sub and deploy the message box.

[snb]

Why declaring variables and initialising variabels if you don't use them ?


Private Sub cmdent_Click()
on error resume next

With sheets("Budgets").columns(1).find(sheets("Pref").Range("o13").Value)
if err.number=0 then
if .offset(,1).value=sheets("Pref").Range("o14").Value Then
MsgBox "Budget Already Entered"
exit sub
end if
else
sheets("budgets").Cells(Rows.Count, 1).End(xlUp).Offset(1).resize(33)=sheets("Pref").Range("o13:o46").Value
End If
End With
End Sub

[streub]

Solution is sweet! I will apply and test later.

Hmmm . . . I thought I had declared and initialized the variables in my example. Where am I in error?

As always, many thanks.


Why declaring variables and initialising variabels if you don't use them ?


Private Sub cmdent_Click()
on error resume next

With sheets("Budgets").columns(1).find(sheets("Pref").Range("o13").Value)
if err.number=0 then
if .offset(,1).value=sheets("Pref").Range("o14").Value Then
MsgBox "Budget Already Entered"
exit sub
end if
else
sheets("budgets").Cells(Rows.Count, 1).End(xlUp).Offset(1).resize(33)=sheets("Pref").Range("o13:o46").Value
End If
End With
End Sub

[snb]

Although you set sheets("budgets") to variable ws, you nevertheless use sheets("budgets") in your code....

[streub]

Good grief!

[Ringhal]

If you use ws to declare one worksheet and wk to declare another sheet, you or someone else may get confused when you need to use the variable again as the names aren't clear.

Just a thought.

[streub]

It was an oversight on my part. Typically I declare worksheet names as variables ws, wk, etc. Using the entire worksheet name is often tedious and sing sheet number can also be confusing.

[streub]

I have had to make a few changes to avoid having erroneous budget data entereed. These changes execute as required however, the code to prevent duplicate budget entries and message box do not execute. Lastly, the code used for copying the data transposed in not functioning. I know it is something small but I have not been able to resolve.



Private Sub cmdent_Click()
Dim ws As Worksheet
Set ws = Worksheets("Budgets")
Dim wk As Worksheet
Set wk = Worksheets("Pref")

On Error Resume Next
With wk
If .Range("o11").Value < 1 Then
MsgBox "Budget Total Required to Proceed"
Exit Sub


Else
If .Range("o11").Value <> wk.Range("o45").Value Then
MsgBox "Budget Figures Do Not Balance"
Exit Sub


Else

With ws.Columns(1).Find(wk.Range("o12").Value)
If Err.Number = 0 Then
If .Offset(, 1).Value = wk.Range("o13").Value Then
MsgBox "Budget Already Entered"
Exit Sub

Else
' ws.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize (33)
ws.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(33).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=True = wk.Range("o12:o45").Value


End If

End If
End With
End If
End If
End With
End Sub

[snb]

Please read my suggestion again.
I do not copy, but put the values in another range:


Private Sub cmdent_Click()
Set ws = Worksheets("Budgets")
Set wk = Worksheets("Pref")

On Error Resume Next
If wk.Range("o11").Value < 1 Then c00="Budget Total Required to Proceed"
If wk.Range("o11").Value <> wk.Range("o45").Value Then c00= "Budget Figures Do Not Balance"

If c00<>"" then
With ws.Columns(1).Find(wk.Range("o12").Value)
If Err.Number = 0 Then
If .Offset(, 1).Value = wk.Range("o13").Value Then c00= "Budget Already Entered"
Else
ws.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(33)= wk.Range("o12:o45").Value
end if
end with
End If

if c00<>"" then msgbox c00
End Sub

[streub]

Please pardon my error. Your code is exceptional and I was merely attempting to adapt it to my needs. The budget sheet is designed using rows rather than columns hence the need to transpose. Again, I should have been more precise. Where would I append this sub to transpose:


ws.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(33)= wk.Range("o12:o45").Value




Please read my suggestion again.
I do not copy, but put the values in another range:


Private Sub cmdent_Click()
Set ws = Worksheets("Budgets")
Set wk = Worksheets("Pref")

On Error Resume Next
If wk.Range("o11").Value < 1 Then c00="Budget Total Required to Proceed"
If wk.Range("o11").Value <> wk.Range("o45").Value Then c00= "Budget Figures Do Not Balance"

If c00<>"" then
With ws.Columns(1).Find(wk.Range("o12").Value)
If Err.Number = 0 Then
If .Offset(, 1).Value = wk.Range("o13").Value Then c00= "Budget Already Entered"
Else
ws.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(33)= wk.Range("o12:o45").Value
end if
end with
End If

if c00<>"" then msgbox c00
End Sub

[snb]

ws.Cells(Rows.Count, 1).End(xlUp).Offset(1).Resize(,33)= application.transpose(wk.Range("o12:o45").Value)

[streub]

Thank you.