VBA ARRAY CODE HELP

[roykana]

Dear all master,
Please help for vba array code remove numbers based on specific contents of cell.

ORIIGINAL

RESULT

So remove the numbers based on the contents of the word "KANA".


Thanks

roykana

 

[roykana]

Any good reason why the 20+ lines of code from Post #2 are repeated in Post #3?
Any good reason why the 30+ lines of code from Post #4 are repeated in Post #5?
Just read the very first line of Post #4 again.

Dear jolivanes,

sorry if i bother you.
Previously I was waiting for your reply
I marked the solution on your behalf because you created the source code.

Sub Maybe_So()
Dim c As Range
    For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        If Left(c, 4) = "KANA" Then c.Value = Left(c, InStrRev(c, " ") - 1)
    Next c
End Sub

Sub Or_Maybe_So()
Dim aArr, i As Long
aArr = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).Value
    For i = LBound(aArr) To UBound(aArr)
        If Left(aArr(i, 1), 4) = "KANA" Then aArr(i, 1) = Left(aArr(i, 1), InStrRev(aArr(i, 1), " ") - 1)
    Next i
Range("A1").Resize(UBound(aArr)) = aArr
End Sub

for sub code "Sub Or_Maybe_So() " this is ok, it can be used for 100,000 rows but why when it is run it removes the table style format

Sub Or_Maybe_Even_So()
Const strCol = "A" ' column
Const strText = "KANA" ' text to look for
Dim lngLast As Long, c As Range
lngLast = Range(strCol & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
    With Range(strCol & "1:" & strCol & lngLast)
        .AutoFilter Field:=1, Criteria1:=strText & "*"
            For Each c In Range("A2:A" & lngLast).SpecialCells(12)    'strCol & "1:" & strCol & lngLast).Offset(1).SpecialCells(12)
                c.Value = Left(c, InStrRev(c, " ") - 1)
            Next c
        .AutoFilter
    End With
Application.ScreenUpdating = True
End Sub

one more question I have if the text string is more than one then I use each sub call from the sub code or is there another solution from you.
thanks
roykana

 

[jolivanes]

You are not reading our responses.

 

[roykana]

Any good reason why the 20+ lines of code from Post #2 are repeated in Post #3?

in post 2# it doesn't set the text in the code and it runs at 5000 lines very slowly

Any good reason why the 30+ lines of code from Post #4 are repeated in Post #5?

in post 4# with 100,000 records, the error is in the "Sub Or_Maybe_So()" code, which is run time error 13

Just read the very first line of Post #4 again.

this is what you mean "Don't quote the whole post. Just a bunch of extra clutter.
See Post number if needed."
sorry if there is a miss communication with you. I'm just a beginner and want to learn a lot from a master like you.

thanks
roykana

 

[roykana]

You are not reading our responses.

one more thing you should know I'm just waiting for a reply from you so I don't really respond to others. Because I appreciate the vba code you made for me

 

[roykana]

Dear jolivanes,

sorry if i bother you.
Previously I was waiting for your reply
I marked the solution on your behalf because you created the source code.

Sub Maybe_So()
Dim c As Range
    For Each c In Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row)
        If Left(c, 4) = "KANA" Then c.Value = Left(c, InStrRev(c, " ") - 1)
    Next c
End Sub

Sub Or_Maybe_So()
Dim aArr, i As Long
aArr = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).Value
    For i = LBound(aArr) To UBound(aArr)
        If Left(aArr(i, 1), 4) = "KANA" Then aArr(i, 1) = Left(aArr(i, 1), InStrRev(aArr(i, 1), " ") - 1)
    Next i
Range("A1").Resize(UBound(aArr)) = aArr
End Sub

for sub code "Sub Or_Maybe_So() " this is ok, it can be used for 100,000 rows but why when it is run it removes the table style format

Sub Or_Maybe_Even_So()
Const strCol = "A" ' column
Const strText = "KANA" ' text to look for
Dim lngLast As Long, c As Range
lngLast = Range(strCol & Rows.Count).End(xlUp).Row
Application.ScreenUpdating = False
    With Range(strCol & "1:" & strCol & lngLast)
        .AutoFilter Field:=1, Criteria1:=strText & "*"
            For Each c In Range("A2:A" & lngLast).SpecialCells(12)    'strCol & "1:" & strCol & lngLast).Offset(1).SpecialCells(12)
                c.Value = Left(c, InStrRev(c, " ") - 1)
            Next c
        .AutoFilter
    End With
Application.ScreenUpdating = True
End Sub

one more question I have if the text string is more than one then I use each sub call from the sub code or is there another solution from you.
thanks
roykana

can you answer me so I can mark the solution on your behalf.
Thanks
roykana

 

[jolivanes]

Once when you go through all your posts and delete all the quoted sections.
In Post #13 you acknowledged that you knew what it was all about and in Posts #14 and #15 you quoted again, and to top it all off, you quoted yourself in Post #15!

 

[Fluff]

Once when you go through all your posts and delete all the quoted sections.

And how is the OP meant to do that, when you only have 10mins to edit a post?

 

[roykana]

Once when you go through all your posts and delete all the quoted sections.
In Post #13 you acknowledged that you knew what it was all about and in Posts #14 and #15 you quoted again, and to top it all off, you quoted yourself in Post #15!

you can provide the latest post with your code fix and I ask you if you want to fix the code and answer my question

 

[roykana]

I have not been able to reproduce that error on that line of the code, even with 1000,000 rows.

Does it make any difference if you change that marked line to this?

Range("A1").Resize(UBound(aArr)) = aArr

Do you have any error values in the column?

Dear Peter_SSs,
only you can solve my problem.

Sub Or_Maybe_So()
Dim aArr, i As Long
aArr = Range("A1:A" & Cells(Rows.Count, 1).End(xlUp).Row).Value
    For i = LBound(aArr) To UBound(aArr)
        If Left(aArr(i, 1), 4) = "KANA" Then aArr(i, 1) = Left(aArr(i, 1), InStrRev(aArr(i, 1), " ") - 1)
    Next i
Range("A1").Resize(UBound(aArr)) = aArr
End Sub

from the code above there is no error but it removes the style format table.

you can make your own version of the vba array code so that the full vba code from you.

if the text is searched for more than one, what do I make like the vba code below or do you have another solution?

Sub tocall()
Call Or_Maybe_So1
Call Or_Maybe_So2
Call Or_Maybe_So3
Call Or_Maybe_So4
Call Or_Maybe_So5
End Sub

Thanks
roykana

 

[jolivanes]

@Fluff
Can't you spot him/her a little extra time?