PDA

View Full Version : How to include a dash in my search - VBA?



Zrob
12-10-2008, 06:14 PM
Hey Guys!

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

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?
If Cell.Value Like "7441702242-M" Then

Here is all the code:

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


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 :dunno 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:


Public Sub mSum()

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

End Sub


Thanks for any help with this.

Rob

Krishna Kumar
12-10-2008, 07:24 PM
Hi,

try,

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("D1:D23"))
.Offset(, 2).Value = Application.WorksheetFunction.Sum(ws.Range("E1:E23"))
End With
End Sub
HTH

Zrob
12-10-2008, 07:57 PM
Thanks for the help!! :friends:

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

Rob

Krishna Kumar
12-11-2008, 01:58 AM
Hi,

See Like Operator in VBA help.

Zrob
12-11-2008, 05:45 AM
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>

.Resize(n, UBound(SiteCol, 2)).Value = w
Thanks.

Zrob
12-11-2008, 11:50 AM
Its working now, I must have had somthing set wrong.

Zrob
12-11-2008, 02:33 PM
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
12-11-2008, 04:22 PM
Start from the right
Columns("F:J").Delete
Columns("B:D").Delete

Zrob
12-11-2008, 08:08 PM
Start from the right
Columns("F:J").Delete
Columns("B:D").Delete

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.




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

mdmackillop
12-11-2008, 11:35 PM
With the column headers selected, you could use

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

Zrob
12-12-2008, 09:10 AM
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


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

Zrob
12-12-2008, 09:27 AM
Update:

None of the variables are being recognized??


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

mdmackillop
12-12-2008, 09:57 AM
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
12-12-2008, 10:25 AM
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

If Selection <> A Or Selection <> B Or Selection <> C Or Selection <> D Then

mdmackillop
12-12-2008, 10:31 AM
Without knowing your requirements, it is difficult to assist. Choose one way for each question, and we can go from there.

Zrob
12-12-2008, 10:44 AM
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
12-12-2008, 12:29 PM
Try this

Zrob
12-12-2008, 04:34 PM
Yeah, that works nice job.

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

mdmackillop
12-12-2008, 05:40 PM
It's not protected, but here it is.
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

Zrob
12-12-2008, 10:04 PM
Thats it?

VB is kinda strange........:banghead:

mdmackillop
12-13-2008, 05:42 AM
That's it. You can do a lot in a few lines!

Zrob
12-14-2008, 07:41 PM
Thanks for the help to both of you!! I have combined the code made some minor changes. Once Inside the VBA file you can run either the module or run the user form which gives the user an option to add his own part number, though I need "error checking on the part number".

Use this in the dialog text box: H421-1M or S334-100

Here it is please take a look and let me know how I could make it better.

Thanks!!

Zrob
12-15-2008, 05:42 AM
One slight problem, when I paste data from a version 2003 worksheet into the above attachment the Arr=0 all the time and deletes all the columns.

Could there be special coding or filters blocking the correct reading on the first row? Here is a part of that file if it uploads....