theguruguy
New Member
- Joined
- Dec 15, 2018
- Messages
- 12
I am trying to take like patterns in cells of a table and copy them to new worksheet. when the matched pattern occurs i need to append the cell in the new worksheet rather than copy over. then i need the script to move to next cell in the row and look for another matched pattern and do the same as above but in the next column of the new worksheet.
here are a few matched patterns I am trying to match are:
grp.app
grp.vdi
grp.ntfs
grp.ctx
there will be additional info at the end of each of those entries, but i just want to collect all the similar ones at that level.
here is a short example of what I have.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]user Id[/TD]
[TD]random groups[/TD]
[TD]random groups[/TD]
[TD]random groups[/TD]
[TD]random groups[/TD]
[/TR]
[TR]
[TD]user1[/TD]
[TD]grp.ctx.1[/TD]
[TD]grp.share.100[/TD]
[TD]grp.app.7[/TD]
[TD]grp.app.15[/TD]
[/TR]
[TR]
[TD]user2[/TD]
[TD]grp.ctx.10[/TD]
[TD]grp.vdi.20[/TD]
[TD]grp.app.10[/TD]
[TD]grp.app.2[/TD]
[/TR]
</tbody>[/TABLE]
This is what i need.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]user Id[/TD]
[TD]Applications[/TD]
[TD]VDI[/TD]
[TD]Fileshare[/TD]
[TD]Citrix[/TD]
[/TR]
[TR]
[TD]user1[/TD]
[TD]grp.app.7
grp.app.15[/TD]
[TD][/TD]
[TD]grp.share.100[/TD]
[TD]grp.ctx.1[/TD]
[/TR]
[TR]
[TD]user2[/TD]
[TD]grp.app.10
grp.app.2[/TD]
[TD] grp.vdi.20[/TD]
[TD][/TD]
[TD]grp.ctx.10[/TD]
[/TR]
</tbody>[/TABLE]
I also need the matching loop to end and go to next row when it reaches an empty column cell value.
once all the matching patterns are copied(appended) to new worksheet. I need the individual cells that have multiple entries sorted alphabetically.
Here is some of the bad code I started. I apologize, I am new to this.
I changed my strategy and went back to the top to try something different. but i am just at a loss.
here are a few matched patterns I am trying to match are:
grp.app
grp.vdi
grp.ntfs
grp.ctx
there will be additional info at the end of each of those entries, but i just want to collect all the similar ones at that level.
here is a short example of what I have.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]user Id[/TD]
[TD]random groups[/TD]
[TD]random groups[/TD]
[TD]random groups[/TD]
[TD]random groups[/TD]
[/TR]
[TR]
[TD]user1[/TD]
[TD]grp.ctx.1[/TD]
[TD]grp.share.100[/TD]
[TD]grp.app.7[/TD]
[TD]grp.app.15[/TD]
[/TR]
[TR]
[TD]user2[/TD]
[TD]grp.ctx.10[/TD]
[TD]grp.vdi.20[/TD]
[TD]grp.app.10[/TD]
[TD]grp.app.2[/TD]
[/TR]
</tbody>[/TABLE]
This is what i need.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]user Id[/TD]
[TD]Applications[/TD]
[TD]VDI[/TD]
[TD]Fileshare[/TD]
[TD]Citrix[/TD]
[/TR]
[TR]
[TD]user1[/TD]
[TD]grp.app.7
grp.app.15[/TD]
[TD][/TD]
[TD]grp.share.100[/TD]
[TD]grp.ctx.1[/TD]
[/TR]
[TR]
[TD]user2[/TD]
[TD]grp.app.10
grp.app.2[/TD]
[TD] grp.vdi.20[/TD]
[TD][/TD]
[TD]grp.ctx.10[/TD]
[/TR]
</tbody>[/TABLE]
I also need the matching loop to end and go to next row when it reaches an empty column cell value.
once all the matching patterns are copied(appended) to new worksheet. I need the individual cells that have multiple entries sorted alphabetically.
Here is some of the bad code I started. I apologize, I am new to this.
Code:
Sub Findandcut()
Dim row As Long
Dim lastrow As Integer
lastrow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).row
Dim org(1 To lastrow) As String
Dim startcol As Integer
startcol = 2
For row = 1 To lastrow
' Check if "condition" appears in the value anywhere.
org(row) = Worksheets("totals").Cells(row, startcol).Value
If Cells(row, startcol).Value Like "*grp.app*" Then
Worksheets("totals").Cells(row, startcol).Value = Cells(row, startcol).Value & ", " & org(row)
ElseIf Cells(row, startcol).Value Like "*ctx*" Then
startcol = startcol + 1
Worksheets("totals").Cells(row, startcol).Value = Cells(row, startcol).Value & ", " & org(row)
ElseIf Cells(row, startcol).Value Like "*grp.vdi*" Then
startcol = startcol + 2
Worksheets("totals").Cells(row, startcol).Value = Cells(row, startcol).Value & ", " & org(row)
Range("EC" & row).Copy Worksheets("totals").Range("B" & row)
ElseIf Range("B" & row).Value Like "*ntfs*" Then
Range("ED" & row).Value = Range("B" & row).Value & ", " & org4
End If
Next
I changed my strategy and went back to the top to try something different. but i am just at a loss.