Forum Discussion
hrh_dash
Jun 30, 2022Iron Contributor
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