A macro to cut & paste in same sheet

September 28, 2011 at 07:33:07
Specs: Windows XP
 excel using macros to filter dataI 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

See More: A macro to cut & paste in same sheet

#1
September 29, 2011 at 01:14:49
 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 ```

Report •

#2
September 29, 2011 at 11:00:08
 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

Report •

Related Solutions