• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Need help in creating VBA code to replace the filtered cells in sheet1 with values from another column of sheet2

rihana

New Member
In sheet1 , i have a column with 300 rows, i need to filter this column with criteris "REF*D9*" and then replace all the filtered cells with values from another column of sheet2

attached the sample workbook, likewise i need to replace for so many records('i.e., its not just for 3)
 

Attachments

vletm

Excel Ninja
rihana
Questions:
What to do with values eg Sheet2's A5? .. how to know which values stays in Sheet2? ... now Sheet2 has four REF*09...
Is it really replace something (could those eg add in the end of sheet?)
or
delete from Sheet1 and copy from Sheet2 to Sheet1?
 

Marc L

Excel Ninja
rihana, it's weird to manually filter data then use a VBA procedure​
as it can be easily done without filtering before the execution (and neither during the execution) …​
Anyway, according to your will from a filter, a beginner starter demonstration :​
Code:
Sub Demo1()
    Dim L&, F&, N&, R&
        L = Sheet2.UsedRange.Rows.Count
    With Sheet1
        F = Application.Subtotal(103, .UsedRange.Columns(1))
        If F = 1 Or L = 1 Or Not .FilterMode Then Beep: Exit Sub
        Application.ScreenUpdating = False
        N = 1
    For R = 2 To .[A1].End(xlDown).Row
        If Not .Rows(R).Hidden Then
            N = N + 1
            Sheet2.Cells(N, 1).Copy .Cells(R, 1)
            If N = F Or N = L Then Exit For
        End If
    Next
        Application.ScreenUpdating = True
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 

rihana

New Member
rihana
Questions:
What to do with values eg Sheet2's A5? .. how to know which values stays in Sheet2? ... now Sheet2 has four REF*09...
Is it really replace something (could those eg add in the end of sheet?)
or
delete from Sheet1 and copy from Sheet2 to Sheet1?
Ignore Sheet2's A5 as it is extra value, i.e,
>>if sheet2's A coloumn has 10 rows and sheet1's A has 7 Ref*D9 segments then my expectation is macro should replace all 7 REF*D9 segments with 1st 7 segments of Sheet2's A and ignore A8, A9 and A10
>> >>if sheet2's A coloumn has 10 rows and sheet1's A has 12 Ref*D9 segments then my expectation is macro should replace 1st 10 REF*D9 segments with corresponding 10 segments of Sheet2's A and color the Sheet1's 11th and 12th REF*D9 segments
>> Its just a replace no need to delete
 

rihana

New Member
rihana, it's weird to manually filter data then use a VBA procedure​
as it can be easily done without filtering before the execution (and neither during the execution) …​
Anyway, according to your will from a filter, a beginner starter demonstration :​
Code:
Sub Demo1()
    Dim L&, F&, N&, R&
        L = Sheet2.UsedRange.Rows.Count
    With Sheet1
        F = Application.Subtotal(103, .UsedRange.Columns(1))
        If F = 1 Or L = 1 Or Not .FilterMode Then Beep: Exit Sub
        Application.ScreenUpdating = False
        N = 1
    For R = 2 To .[A1].End(xlDown).Row
        If Not .Rows(R).Hidden Then
            N = N + 1
            Sheet2.Cells(N, 1).Copy .Cells(R, 1)
            If N = F Or N = L Then Exit For
        End If
    Next
        Application.ScreenUpdating = True
    End With
End Sub
Do you like it ? So thanks to click on bottom right Like !​
Im trying to understand this
 

rihana

New Member
rihana
Gotta guess the rest
but
something like this ..
Note: Sheet2's B1 need to be filled!
Yeah this seems to be fine, but this script is working only for the first 4 findings of Sheet1.

Ex: I have now 6 REF*D9 segments in sheet1, and sheet2's A coloumn has 10 rows but script is replacing only first 4.

i.e., i need a script for 'n' no.of findings, count is not limited(it can be 1, 2, 10, 100...n)
 

rihana

New Member
Yeah this seems to be fine, but this script is working only for the first 4 findings of Sheet1.

Ex: I have now 6 REF*D9 segments in sheet1, and sheet2's A coloumn has 10 rows but script is replacing only first 4.

i.e., i need a script for 'n' no.of findings, count is not limited(it can be 1, 2, 10, 100...n)
 

Attachments

vletm

Excel Ninja
rihana
The 1st is always a Sample
and I don't test all [im]possible variations.
You didn't modify code.
Test this.
I used my file.
... and it will swap those values (then You can have 'undo' too).
 

Attachments

Last edited:
Top