Forum Discussion

hrh_dash's avatar
hrh_dash
Iron Contributor
Jun 30, 2022

Unable to find and paste data via VBA

Currently i have a vba code which execute a Find function. So when "PAID-UP ,ORDINARY" is found and if the next column is "SINGAPORE, DOLLARS", it will  then copy the info in the columns.

 

For example:

Ctrl Find: "PAID-UP ,ORDINARY", A1 contains "PAID-UP ,ORDINARY", check whether B1 = "SINGAPORE, DOLLARS", if true then copy data in C1. 

 

Else if 

A1 contains "PAID-UP ,ORDINARY", check whether B1 = "SINGAPORE, DOLLARS" and C1 is blank, copy data from E2.

 

However, now i would like to have 2 Cltrl Find for "PAID-UP ,ORDINARY"; one for in USD and the other in SGD.

 

If its true for :

1st Cltrl find for "PAID-UP ,ORDINARY" and if next column is "UNITED STATES OF AMERICA, DOLLARS" +

2nd Cltrl find for "PAID-UP ,ORDINARY" and if next column is "SINGAPORE, DOLLARS" 

then data for the 1st Cltrl find will be multiply by 1.4 + data from the 2nd Cltrl find

 

else

 

Cltrl find for "PAID-UP ,ORDINARY" and if next column is "SINGAPORE, DOLLARS" and next column is blank, copy data from another column. Eg; A1 = PAID-UP ,ORDINARY, B1 = "SINGAPORE, DOLLARS", C1 = "" and D1 = 50,000, copy data in D1 and paste in another worksheet.

 

Code as follows:

Dim rngfind4 As Range
Dim rngfind5 As Range
Dim wsS As Worksheet
Dim wsD As Worksheet
Dim curR As String

Set wsD = Sheet1
Set wsS = Sheet5

With wsS.Cells

curR = InputBox("Please input currency rate")

Set rngfind5 = .Find(What:="PAID-UP ,ORDINARY", After:=ActiveCell, LookIn:=xlValues _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)


Set rngfind5 = .FindNext(rngfind5)


Set rngfind4 = .Find(What:="PAID-UP ,ORDINARY", After:=ActiveCell, LookIn:=xlValues _
        , LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False)
        
        
If rngfind5.Offset(0, 1) = "UNITED STATES OF AMERICA, DOLLARS" Then

wsD.Range("C17") = (rngfind5.Offset(0, 2) * curR) + rngfind4.Offset(0, 2) ' <-- this code works

If (rngfind5.Offset(0, 1) <> "UNITED STATES OF AMERICA, DOLLARS" Or rngfind5.Offset(0, 1) Is Nothing) And rngfind4.Offset(0, 1) = "SINGAPORE, DOLLARS" <> "" Then 

rngfind4.Offset(0, 2).copy
wsD.Range("C17").PasteSpecial Paste:=xlPasteValues 'this code doesn't work

If (rngfind5.Offset(0, 1) <> "UNITED STATES OF AMERICA, DOLLARS" Or rngfind5.Offset(0, 1) Is Nothing) And rngfind4.Offset(0, 1) = "SINGAPORE, DOLLARS" <> "" And rngfind4.Offset(0, 2) <> "" Then 

rngfind4.Offset(0, 2).copy
wsD.Range("C17").PasteSpecial Paste:=xlPasteValues 'this code doesn't work

ElseIf (rngfind5.Offset(0, 1) <> "UNITED STATES OF AMERICA, DOLLARS" Or rngfind5.Offset(0, 1) Is Nothing) And rngfind4.Offset(0, 1) = "SINGAPORE, DOLLARS" <> "" And rngfind4.Offset(0, 2) = "" Then

rngfind4.Offset(-1, 4).copy
wsD.Range("C17").PasteSpecial Paste:=xlPasteValues 'this code doesn't work

End If
End If
End If


End With

 

Appreciate and thanks for the help in advance!

No RepliesBe the first to reply

Resources