vba Copy Paste Conditional to put remark 1 2 3 .. etc

**fixer** · 06-28-2020, 07:32 PM

Hello, Need help regarding the macro

Code:

Sub STEP29()
Dim wb1 As Workbook, wb2 As Workbook, Ws1 As Worksheet, Ws2 As Worksheet
 Set wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Alert..csv")
 Set wb2 = ThisWorkbook
 Set Ws1 = wb1.Worksheets.Item(1): Set Ws2 = wb2.Worksheets.Item(1)
Dim arr1() As Variant, arr2() As Variant, arr3() As Variant
 Let arr1() = Ws1.Range("A1:K" & Ws1.Range("A1").CurrentRegion.Rows.Count & "").Value
 Let arr2() = Ws2.Range("A1").CurrentRegion.Value
 ReDim arr3(0 To UBound(arr2(), 1))
Dim Cnt
    For Cnt = 2 To UBound(arr2(), 1)
    Dim Lc As Long: Let Lc = Ws2.Cells.Item(Cnt, Ws2.Cells.Columns.Count).End(xlToLeft).Column
     Let arr3(Cnt - 1) = Ws2.Range("A" & Cnt & ":" & CL(Lc + 1) & Cnt & "").Value
     Let arr3(Cnt - 1)(1, UBound(arr3(Cnt - 1), 2)) = UBound(arr3(Cnt - 1), 2) - 2
    Dim mtchRes As Variant
     Let mtchRes = Application.Match(arr2(Cnt, 2), Ws1.Range("B1:B" & UBound(arr1(), 1) & ""), 0)
        If IsError(mtchRes) Then
        ' a match was not found, so we do not need to remove the  1   2   3   etc...
        Else
        ' a match was found, so we need to remove the  1   2   3   etc...
        Dim Empt As Long
            For Empt = 3 To UBound(arr3(Cnt - 1), 2)
             Let arr3(Cnt - 1)(1, Empt) = ""
            Next Empt
        End If
    '3c) Paste out row
    Let Ws2.Range("A" & Cnt & "").Resize(1, Lc + 1).Value = arr3(Cnt - 1)
    Next Cnt
wb1.Close
wb2.Save

                                                                                                                        
                                                                                                                        
                                                                                                                        
End Sub



















Public Function CL(ByVal lclm As Long) As String
    Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
End Function

Actually i converted that Alert..csv file to Alert..xls
So I need the changes in the macro according to that
So Plz have a look and help me in solving this problem Sir

**Molly Brennholz** · 06-30-2020, 01:47 PM

Hi,
I am not quite sure what you are asking…
( Your macro above seems similar to some which I did for you ( https://excelfox.com/forum/showthrea...mark-1-2-3-etc ) )

Probably all you would need to do is use the File name of the File that you are using
Set wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Alert..xls")
But that is obvious. So possibly you are trying to ask something else.

( Of course, if you have been .Opening .csv text files with an Excel object, as in your macro above in post #1, ( https://excelfox.com/forum/showthrea...ll=1#post14144 ) then you may have got sometimes problems.
But I am not going to make any attempt to explain that to you, since I can see from your recent posts here and elsewhere that many people have tried very hard to explain that all to you, and you still don’t seem to have understood anything !!! )
Molly

**fixer** · 06-30-2020, 05:57 PM

Sorry let me explain once again Molly Mam

This macro is made for alert..csv file

Code:

Sub STEP29()
Dim wb1 As Workbook, wb2 As Workbook, Ws1 As Worksheet, Ws2 As Worksheet
 Set wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Alert..csv")
 Set wb2 = ThisWorkbook
 Set Ws1 = wb1.Worksheets.Item(1): Set Ws2 = wb2.Worksheets.Item(1)
Dim arr1() As Variant, arr2() As Variant, arr3() As Variant
 Let arr1() = Ws1.Range("A1:K" & Ws1.Range("A1").CurrentRegion.Rows.Count & "").Value
 Let arr2() = Ws2.Range("A1").CurrentRegion.Value
 ReDim arr3(0 To UBound(arr2(), 1))
Dim Cnt
    For Cnt = 2 To UBound(arr2(), 1)
    Dim Lc As Long: Let Lc = Ws2.Cells.Item(Cnt, Ws2.Cells.Columns.Count).End(xlToLeft).Column
     Let arr3(Cnt - 1) = Ws2.Range("A" & Cnt & ":" & CL(Lc + 1) & Cnt & "").Value
     Let arr3(Cnt - 1)(1, UBound(arr3(Cnt - 1), 2)) = UBound(arr3(Cnt - 1), 2) - 2
    Dim mtchRes As Variant
     Let mtchRes = Application.Match(arr2(Cnt, 2), Ws1.Range("B1:B" & UBound(arr1(), 1) & ""), 0)
        If IsError(mtchRes) Then
        ' a match was not found, so we do not need to remove the  1   2   3   etc...
        Else
        ' a match was found, so we need to remove the  1   2   3   etc...
        Dim Empt As Long
            For Empt = 3 To UBound(arr3(Cnt - 1), 2)
             Let arr3(Cnt - 1)(1, Empt) = ""
            Next Empt
        End If
    '3c) Paste out row
    Let Ws2.Range("A" & Cnt & "").Resize(1, Lc + 1).Value = arr3(Cnt - 1)
    Next Cnt
wb1.Close
wb2.Save

                                                                                                                        
                                                                                                                        
                                                                                                                        
End Sub



















Public Function CL(ByVal lclm As Long) As String
    Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
End Function

But now alert..csv file is replaced with alert.xls
so the macro would be

Code:

Sub STEP29()
Dim wb1 As Workbook, wb2 As Workbook, Ws1 As Worksheet, Ws2 As Worksheet
 Set wb1 = Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Alert.xls")
 Set wb2 = ThisWorkbook
 Set Ws1 = wb1.Worksheets.Item(1): Set Ws2 = wb2.Worksheets.Item(1)
Dim arr1() As Variant, arr2() As Variant, arr3() As Variant
 Let arr1() = Ws1.Range("A1:K" & Ws1.Range("A1").CurrentRegion.Rows.Count & "").Value
 Let arr2() = Ws2.Range("A1").CurrentRegion.Value
 ReDim arr3(0 To UBound(arr2(), 1))
Dim Cnt
    For Cnt = 2 To UBound(arr2(), 1)
    Dim Lc As Long: Let Lc = Ws2.Cells.Item(Cnt, Ws2.Cells.Columns.Count).End(xlToLeft).Column
     Let arr3(Cnt - 1) = Ws2.Range("A" & Cnt & ":" & CL(Lc + 1) & Cnt & "").Value
     Let arr3(Cnt - 1)(1, UBound(arr3(Cnt - 1), 2)) = UBound(arr3(Cnt - 1), 2) - 2
    Dim mtchRes As Variant
     Let mtchRes = Application.Match(arr2(Cnt, 2), Ws1.Range("B1:B" & UBound(arr1(), 1) & ""), 0)
        If IsError(mtchRes) Then
        ' a match was not found, so we do not need to remove the  1   2   3   etc...
        Else
        ' a match was found, so we need to remove the  1   2   3   etc...
        Dim Empt As Long
            For Empt = 3 To UBound(arr3(Cnt - 1), 2)
             Let arr3(Cnt - 1)(1, Empt) = ""
            Next Empt
        End If
    '3c) Paste out row
    Let Ws2.Range("A" & Cnt & "").Resize(1, Lc + 1).Value = arr3(Cnt - 1)
    Next Cnt
wb1.Close
wb2.Save

                                                                                                                        
                                                                                                                        
                                                                                                                        
End Sub



















Public Function CL(ByVal lclm As Long) As String
    Do: Let CL = Chr(65 + (((lclm - 1) Mod 26))) & CL: Let lclm = (lclm - (1)) \ 26: Loop While lclm > 0
End Function

Any more changes is required in this code then plz let me know Molly Mam

**Molly Brennholz** · 07-01-2020, 11:36 AM

I think I understood already what you are asking.
I think you are still confused and mixed up because you have still not understood anything about the difference between Excel files and text files.
You sometimes tell us that you understand. But all your questions suggest to us that you still have not understood anything at all.

If I try to explain again, you will probably just get more confused or, more likely, you will not read or understand anything I tell you. So it will just waste all our time again.

Originally Posted by fixer

...This macro is made for alert..csv file...

Are you sure?
I do not think so.
Who made the macro for alert..csv file?

I did not make any macro for you to work on a text ( .csv ) file.
Who made this macro for you?
It looks very similar to macros which I made for you. But all the macros I made for you were for Excel files. I never made any macros for you to work with text files

If you are lucky,

Code:

Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Alert.xls")

may sometimes do the same as what you want

Code:

Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Alert..csv")

to do.

So if you are lucky, that will be the only change that you need.

But it is very dangerous and stupid to ever do
Workbooks.Open("C:\Users\WolfieeeStyle\Desktop\Alert..csv")
( That code line is using the Excel object to .Open a text file. Excel is not really supposed to do that. Excel is mostly supposed to open Excel files. But Excel will allow you to try to open a text file. Excel will try to guess what to do. It may try to do what you want. Sometimes it may do different things. Many people have tried very hard to explain that in detail to you for 2 months already).

That code line may sometimes do what you want. But there will probably always be problems.
You seem to have decided not to want to know anything about such problems. So it may be impossible for anyone to really help you anymore.

Remember one last very important thing:
You are not the only person that understands nothing about the difference between Excel files and text files.
Lots of people understand nothing about the difference between Excel files and text files.

So if you continually cross post and ask the same question at different places, you will eventually find someone that will say to you.
…Yes, that is the only change needed .
So you will be happy then. But it is the wrong answer.

But that is your choice. You should do what you want to. You prefer a simple short answer, even if it is the wrong answer.

It is nice to have different people in this world. Stay as you want to be. (You will never achieve your aim, unless your aim is to fail and get the wrong results sometimes).
But you should stay as you want to be. The world would be very boring if we all were the same.

Good luck in your future failures!

Molly

**fixer** · 07-01-2020, 04:00 PM

I made a Mistake & I corrected My Mistake
I am not working with any .csv file
& In Future I will never Ask any question related to .csv file
Thnx Alot Molly Mam Doubts & Problem both are solved