How to include a dash in my search - VBA?

[Zrob]

Hey Guys!

Thought I would try my hand again at this VBA stuff....

Two parts, please see bellow for more....

Though I need some help, bellow is some code slightly modified tutorial, but I noticed that when I search for a part number with a dash it does not work, if there are no special characters it works fine, how can I fix this part of it?
[VBA]If Cell.Value Like "7441702242-M" Then[/VBA]

Here is all the code:
[VBA]
Option Explicit

Public Sub copyRows()

Dim SiteCol As Range, Cell As Object
Dim NewSh As Worksheet

Set SiteCol = Range("A:A") 'Range containing pc names

Set NewSh = Worksheets.Add

For Each Cell In SiteCol

If IsEmpty(Cell) Then
Exit Sub
End If

If Cell.Value Like "7441702242-M" Then
'If Cell.Value Like "PN" Then
Cell.EntireRow.Copy

ActiveSheet.Range("A10000").End(xlUp).Select
Selection.Offset(1, 0).Select
NewSh.Paste
Application.CutCopyMode = False

End If

Next

End Sub
[/VBA]

Second part:
Once I run the above code I need to run a second sub to create some totals; I am doing it this way because I really don't know what I am doing yet sorry!

My question is how can I run this code within the above code of can I call it to run, or should I make a function?? I tried embedding it into the above code but it would not run? Here is the Code:

[VBA]
Public Sub mSum()

Range("C23").Value = Application.WorksheetFunction.Sum(Range("C1:C23"))
Range("D23").Value = Application.WorksheetFunction.Sum(Range("D123"))
Range("E23").Value = Application.WorksheetFunction.Sum(Range("E1:E23"))

End Sub
[/VBA]

Thanks for any help with this.

Rob

[Krishna Kumar]

Hi,

try,

[vba]Option Explicit

Public Sub CopyRows()

Dim SiteCol, i As Long, w(), n As Long
Dim NewSh As Worksheet, c As Long, ws As Worksheet

Const PN As String = "7441702242-M"

Set ws = ActiveSheet
SiteCol = ws.Range("A1", ws.Range("A1").SpecialCells(xlCellTypeLastCell)) 'Range containing pc names

Set NewSh = Worksheets.Add

ReDim w(1 To UBound(SiteCol), 1 To UBound(SiteCol, 2))

For i = 1 To UBound(SiteCol)
If Not IsEmpty(SiteCol(i, 1)) Then
If InStr(1, SiteCol(i,1), PN, vbTextCompare) Then
n = n + 1
For c = 1 To UBound(SiteCol, 2)
w(n, c) = SiteCol(i, c)
Next
End If
End If
Next
With NewSh.Range("a1")
.Resize(n, UBound(SiteCol, 2)).Value = w
End With
With ws.Range("C23")
.Value = Application.WorksheetFunction.Sum(ws.Range("C1:C23"))
.Offset(, 1).Value = Application.WorksheetFunction.Sum(ws.Range("D123"))
.Offset(, 2).Value = Application.WorksheetFunction.Sum(ws.Range("E1:E23"))
End With
End Sub[/vba]
HTH

[Zrob]

Thanks for the help!!

Looks like I needed a few things though. What was my problem with the dash?

Rob

[Krishna Kumar]

Hi,

See Like Operator in VBA help.

[Zrob]

I will do that; One problem though trying to run this, how can I fix this part? It seems to choke at the end where the .Value = w

the error says: <Application-defined or Object-defined error>

[vba] .Resize(n, UBound(SiteCol, 2)).Value = w [/vba]
Thanks.

[Zrob]

Its working now, I must have had somthing set wrong.

[Zrob]

would you know how we can delete certain columns based on a range like:

Delete entire column B through D and then F through J? Or is this really difficult to do??

Thanks again!

[mdmackillop]

Start from the right
[VBA]Columns("F:J").Delete
Columns("B").Delete[/VBA]

[Zrob]

Start from the right
[vba]Columns("F:J").Delete
Columns("B").Delete[/vba]

Yeah you know I was doing that from the left.......no good!
Thanks for that tip.
This is what I was thinking of doing though I might need some help. First of all does this in vba <> mean this != ?? So anyways I was thinking I would like to delete a column if the cells in row 1 (title) was not what I wanted using if statements


Sorry for this lame example, but I do not have VBA at home I will add to this tomorow.


[VBA]

Dim 1, 2

If Selection.Value <> 1 Then
delete column
Selection.Offset(0, 1)
ElseIf Selection.Value <> 2 Then
delete column
Selection.Offset(0, 1)

End If

[/VBA]

[mdmackillop]

With the column headers selected, you could use
[VBA]
Sub DelSelectedColumns()
For i = Selection.Cells.Count To 1 Step -1
If Selection(i) <> 1 Then
Selection(i).EntireColumn.Delete
End If
Next
End Sub

[/VBA]

[Zrob]

Here is what I have today, its half working but for some reason the title in cell A1 is "PN" for part number, BUT this program is not picking that up? When it comes accross to the cell that has 779 ACTL, that part works, I don't get it??

Basically I am just reading the cells in row 1 working my way accros if its a value I want I offset over if its a value I don't want then I delete the whole Column. I have about 50 columns and I only want to keep about 5 so I will be adding a few more if statements once I get this part working.

Thanks

[VBA]
Option Explicit
Sub delColumns()

Range("A1").Select

Const A As String = "779 ACTL"
'Const P As String = "PN"
Dim P As Variant
P = "PN"

'MsgBox "Test: " & P

Dim i
Do Until i = 57

If Selection = P Then
ActiveCell.Offset(0, 1).Select
i = i + 1
End If

If Selection <> A Then
Selection.EntireColumn.Delete
i = i + 1

End If
Loop

End Sub
[/VBA]

[Zrob]

Update:

None of the variables are being recognized??

[VBA]
Option Explicit

Sub delColumns()

Range("B1").Select
Const A As String = "779 ACTL"
Const B As String = "780 ACTL"
Const C As String = "781 ACTL"
Const D As String = "782 ACTL"
'Const P As String = "PN"
Dim P As Variant
P = "PN"

'MsgBox "Test: " & P

Dim i
Do Until i = 57
If Selection = A Or Selection = B Or Selection = C Or Selection = D Then
ActiveCell.Offset(0, 1).Select
i = i + 1
'Selection(i).EntireColumn.Delete
'MsgBox "Test: 2 "
'Else
'Selection.EntireColumn.Delete
End If

If Selection <> A Or Selection = B Or Selection = C Or Selection = D Then
Selection.EntireColumn.Delete
i = i + 1

End If

Loop

End Sub
[/VBA]

[mdmackillop]

Is it simpler to the the headers you want to retain, or those you want to delete? Will these change? If not, we can build them into the code, otherwise it is better to keep them in a range in the workbook.

[Zrob]

Not sure but, I do wan't to crop it down so to speak. Its not the original I am just trying to extract certain data and do some totals.


This was a typo at the bottom, here is the fix for that
[VBA]
If Selection <> A Or Selection <> B Or Selection <> C Or Selection <> D Then
[/VBA]

[mdmackillop]

Without knowing your requirements, it is difficult to assist. Choose one way for each question, and we can go from there.

[Zrob]

Oh I think I know what you mean, no the name on headers wont change, and I would rather delete columns that I don't need.

I can't upload an example from my work or I would post the excel files for you.

[mdmackillop]

Try this

[Zrob]

Yeah, that works nice job.

Can you post the code, I think you have it password protected.

[mdmackillop]

It's not protected, but here it is.
[VBA]Option Explicit

Sub delColumns()

Dim Arr As Variant
Dim i As Long, chk As Long

Arr = Array("779 ACTL", "780 ACTL", "781 ACTL", "782 ACTL")

On Error Resume Next
For i = 57 To 2 Step -1
chk = 0
chk = Application.Match(Cells(1, i).Text, Arr, 0)
If chk = 0 Then Cells(1, i).EntireColumn.Delete
Next
End Sub[/VBA]

[Zrob]

Thats it?

VB is kinda strange........