Forum Discussion
Am puzzled why did the Msgbox popped up despite conditions being met
I have the code below to copy data from wsS to wsD.
So basically, if "PAID-UP ,ORDINARY" can be found in wsS then it would copy the data 2 columns next and paste as special in wsD.
By right, the Msgbox shouldn't popped up as condition was being met; ie "PAID-UP ,ORDINARY" can be found in wsS.
Dim rngfind4 As Range
Dim wsS As Worksheet
Dim wsD As Worksheet
If Range("C16") = "Private Company Ltd by Shares" Or Range("C16") = "Exempt Company Ltd by Shares" Or Range("C16") = "Public Company Ltd by Shares" Or Range("C16") = "Sole Proprietorship (Owned by Company)" Or Range("C16") = "FOREIGN COMPANY REGISTERED IN SINGAPORE" Or Range("C16") = "Others" Or Range("C16") = "Partnership" Or Range("C16") = "Limited Partnership" Or Range("C16") = "Limited Liability Partnership" Then
If Range("C7") = "Yes" Then
Rows("24:25").EntireRow.Hidden = False
Rows("17").EntireRow.Hidden = False
'Extracting data from Bizfile
With wsS.Cells
Set rngfind4 = .Find(What:="PAID-UP ,ORDINARY", After:=ActiveCell, LookIn:=xlValues _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Not rngfind4 Is Nothing Then
rngfind4.Offset(0, 2).Copy
wsD.Range("C17").PasteSpecial Paste:=xlPasteValues
Else
'MsgBox "PAID-UP CAPITAL not found"
End If
End With
End If
Having the Msgbox popping out when conditions met would give other the wrong message. Appreciate the help!
In the attached file you can click the button in cell E3 to start the macro. Basically i changed line 25 of the code because it was a comment.
2 Replies
- OliverScheurichGold Contributor
In the attached file you can click the button in cell E3 to start the macro. Basically i changed line 25 of the code because it was a comment.
- amit_bholaIron Contributor
hrh_dash , try following
1) First of all, it is a good practice to indent the code to check the if blocks. In the code shared by you one End If is missing, but i believe you just missed to put it in your post.
2) Are you sure all conditions are being met? Have you tried debugging by pressing F8 and checking step by step execution? (or e.g. by putting a suitable debug.print "i came here" sort of statement in the copy-paste block?). Or more simply, did the copy-paste actually execute?
3) You are not setting the SearchFormat parameter of Find method. Try writing below statement before the Set rngfind4 ... statement
Application.FindFormat.Clear