SOLVED

Am puzzled why did the Msgbox popped up despite conditions being met

%3CLINGO-SUB%20id%3D%22lingo-sub-3525073%22%20slang%3D%22en-US%22%3EAm%20puzzled%20why%20did%20the%20Msgbox%20popped%20up%20despite%20conditions%20being%20met%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3525073%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20the%20code%20below%20to%20copy%20data%20from%20wsS%20to%20wsD.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20basically%2C%20if%26nbsp%3B%22PAID-UP%20%2CORDINARY%22%20can%20be%20found%20in%20wsS%20then%20it%20would%20copy%20the%20data%202%20columns%20next%20and%20paste%20as%20special%20in%20wsD.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBy%20right%2C%20the%20Msgbox%20shouldn't%20popped%20up%20as%20condition%20was%20being%20met%3B%20ie%26nbsp%3B%22PAID-UP%20%2CORDINARY%22%20can%20be%20found%20in%20wsS.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3EDim%20rngfind4%20As%20Range%0ADim%20wsS%20As%20Worksheet%0ADim%20wsD%20As%20Worksheet%0A%0AIf%20Range(%22C16%22)%20%3D%20%22Private%20Company%20Ltd%20by%20Shares%22%20Or%20Range(%22C16%22)%20%3D%20%22Exempt%20Company%20Ltd%20by%20Shares%22%20Or%20Range(%22C16%22)%20%3D%20%22Public%20Company%20Ltd%20by%20Shares%22%20Or%20Range(%22C16%22)%20%3D%20%22Sole%20Proprietorship%20(Owned%20by%20Company)%22%20Or%20Range(%22C16%22)%20%3D%20%22FOREIGN%20COMPANY%20REGISTERED%20IN%20SINGAPORE%22%20Or%20Range(%22C16%22)%20%3D%20%22Others%22%20Or%20Range(%22C16%22)%20%3D%20%22Partnership%22%20Or%20Range(%22C16%22)%20%3D%20%22Limited%20Partnership%22%20Or%20Range(%22C16%22)%20%3D%20%22Limited%20Liability%20Partnership%22%20Then%0A%0AIf%20Range(%22C7%22)%20%3D%20%22Yes%22%20Then%0A%0A%20%20%20%20%20%20%20%20Rows(%2224%3A25%22).EntireRow.Hidden%20%3D%20False%0A%20%20%20%20%20%20%20%20Rows(%2217%22).EntireRow.Hidden%20%3D%20False%0A%20%20%20%20%20%20%20%20%0A'Extracting%20data%20from%20Bizfile%0AWith%20wsS.Cells%0A%0ASet%20rngfind4%20%3D%20.Find(What%3A%3D%22PAID-UP%20%2CORDINARY%22%2C%20After%3A%3DActiveCell%2C%20LookIn%3A%3DxlValues%20_%0A%20%20%20%20%20%20%20%20%2C%20LookAt%3A%3DxlPart%2C%20SearchOrder%3A%3DxlByRows%2C%20SearchDirection%3A%3DxlNext%2C%20_%0A%20%20%20%20%20%20%20%20MatchCase%3A%3DFalse)%0A%0AIf%20Not%20rngfind4%20Is%20Nothing%20Then%0Arngfind4.Offset(0%2C%202).Copy%0AwsD.Range(%22C17%22).PasteSpecial%20Paste%3A%3DxlPasteValues%0A%0AElse%0A%0A'MsgBox%20%22PAID-UP%20CAPITAL%20not%20found%22%0A%0AEnd%20If%0A%0AEnd%20With%0A%0AEnd%20If%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHaving%20the%20Msgbox%20popping%20out%20when%20conditions%20met%20would%20give%20other%20the%20wrong%20message.%20Appreciate%20the%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-3525073%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3525412%22%20slang%3D%22en-US%22%3ERe%3A%20Am%20puzzled%20why%20did%20the%20Msgbox%20popped%20up%20despite%20conditions%20being%20met%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3525412%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1393409%22%20target%3D%22_blank%22%3E%40hrh_dash%3C%2FA%3E%26nbsp%3B%2C%20try%20following%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E1)%20First%20of%20all%2C%20it%20is%20a%20good%20practice%20to%20indent%20the%20code%20to%20check%20the%20if%20blocks.%20In%20the%20code%20shared%20by%20you%20one%20End%20If%20is%20missing%2C%20but%20i%20believe%20you%20just%20missed%20to%20put%20it%20in%20your%20post.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E2)%20Are%20you%20sure%20all%20conditions%20are%20being%20met%3F%20Have%20you%20tried%20debugging%20by%20pressing%20F8%20and%20checking%20step%20by%20step%20execution%3F%20(or%20e.g.%20by%20putting%20a%20suitable%20debug.print%20%22i%20came%20here%22%20sort%20of%20statement%20in%20the%20copy-paste%20block%3F).%20Or%20more%20simply%2C%20did%20the%20copy-paste%20actually%20execute%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E3)%20You%20are%20not%20setting%20the%20SearchFormat%20parameter%20of%20Find%20method.%20Try%20writing%20below%20statement%20before%20the%20Set%20rngfind4%20...%20statement%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-visual-basic%22%3E%3CCODE%3EApplication.FindFormat.Clear%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-3525438%22%20slang%3D%22en-US%22%3ERe%3A%20Am%20puzzled%20why%20did%20the%20Msgbox%20popped%20up%20despite%20conditions%20being%20met%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-3525438%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1393409%22%20target%3D%22_blank%22%3E%40hrh_dash%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20file%20you%20can%20click%20the%20button%20in%20cell%20E3%20to%20start%20the%20macro.%20Basically%20i%20changed%20line%2025%20of%20the%20code%20because%20it%20was%20a%20comment.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

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!

2 Replies

@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

 

 

 

best response confirmed by hrh_dash (Contributor)
Solution

@hrh_dash 

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.