A macro to cut & paste in same sheet

Tags:

Score

WSMike September 28, 2011 at 07:33:07 Pacific
Specs: Windows XP

excel using macros to filter data
I need to figure out how to cut part of a cell that meets certain condition and paste it into a different cell on the same row. Basically, I need to strip the sizes off of a stock number and append (or paste if nothing is in column) them into a different column. It needs to check the entire row of column B, up to 9000 lines, for a handful of different sizes and remove the dash and size and paste that part of the cell into the same row in column D.
An example would be: (Column B - Before Macro) RUB1234-toddler (Column D - Before Macro) Red
(Column B After Macro) RUB1234 (Column D After Macro) Red Toddler
I would greatly appreciate any help in solving this problem.
Thanks,
Mike G

Reply ↓ Report •

Score

-1

AlwaysWillingToLearn September 29, 2011 at 01:14:49 Pacific

Here is a quick solution iv put together, this will figure out how many rows of data you have, it will first joing the two strings together to create 'Red Toddler' then it will remove the dash from with the first string. give it a go see what you think.

You may need to adjust this a little to refer to the columns you need it to work on.

Dim BCell As Excel.Range
Dim URange, LRange
Dim NewString, TempString

Private Sub Start()
    
    JoinString
    RemoveDash

End Sub

Private Sub JoinString()

    Set URange = Range("A1")
    Set LRange = Range("A" & GetLast)
    
    For Each BCell In Range(URange, LRange)
        
        NewString = Empty
        TempString = Empty
        
        For i = 1 To Len(BCell.Value)
            
            TempString = Mid(BCell.Value, i, 1)
            
            If TempString = "-" Then
                NewString = Mid(BCell.Value, i + 1, Len(BCell.Value))
                BCell.Offset(0, 1).Value = BCell.Offset(0, 1).Value & " " & NewString
            End If
            
        Next i
    
    Next BCell
 
End Sub

Private Sub RemoveDash()
    
    Set URange = Range("A1")
    Set LRange = Range("A" & GetLast)
    
    For Each BCell In Range(URange, LRange)
        
        NewString = Empty
        TempString = Empty
        
        For i = 1 To Len(BCell.Value)
            
            TempString = Mid(BCell.Value, i, 1)
            
            If TempString <> "-" Then
                NewString = NewString & TempString
                
            End If
            
        Next i
            BCell.Value = NewString
    Next BCell

End Sub

Private Function GetLast() As Integer
    
    Sheet1.Range("A65536").End(xlUp).Select
    GetLast = ActiveCell.Row

End Function

Reply ↓ Report •

Score

WSMike September 29, 2011 at 11:00:08 Pacific

I really appreciate you helping, and I pasted in what you gave me into a new macro, and it worked once, but afterwards I got all kinds of errors. I'm not really good with VB, so the code is pretty much foreign to me. The column B is always used to for our stock #'s and column D is always used to put the color and/or size of the item. The data starts on line 8 and can go up to line 9000, most of the time only a couple of hundred lines though.
How I pasted your info in was I used the record macro button, chose a shortcut key (B) and stopped the recording. Then I clicked the macro button and chose edit.
It looked like this:
_________________________
Sub Macro1()
'
' Macro1 Macro
' fghj
'
' Keyboard Shortcut: Ctrl+b
'
End Sub
_________________
Then I pasted in your info below this. If I recall, I'm getting errors saying only comments can go below a sub or function.
I know that most users here are more advanced than me, and I am trying to learn VB, but for now I just need to solve a problem for my business. I really do appreciate any help in solving this problem.
Mike