Thread: Problem with Macros in VB (2 Parts)
View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.programming
Tom Ogilvy Tom Ogilvy is offline
external usenet poster
  
Posts: 27,285
Default Problem with Macros in VB (2 Parts)
Sub Tester1()
Dim rng As Range, rng1 As Range
Dim rng2 As Range

Set rng = Cells(Rows.Count, 1).End(xlUp)
Set rng1 = rng
If LCase(Trim(rng1.Value)) < "end" Then
Do While LCase(Trim(rng1.Value)) < "end" _
And rng1.Row < 1
Set rng1 = rng1.Offset(-1, 0)
Loop
End If
On Error Resume Next
Set rng2 = Columns(1).SpecialCells(xlBlanks)
On Error GoTo 0
If rng2 Is Nothing Then
MsgBox "No blank cells"
Else
Set rng3 = Intersect(rng2.EntireRow, _
Range("A1", rng1).Offset(0, 4))
rng3.FormulaR1C1 = _
"=ISNUMBER(MATCH(RC[-3],ITPatches!C[-4],0))"
End If


End Sub

Worked for me for Question 1.

--
Regards,
Tom Ogilvy

"Sunnmann " wrote in message
...
This is 2 parts and please let me know if you need anymore information.

*1)* I would like to know what the code to write the funtion

=ISNUMBER(MATCH(RC[-3],ITPatches!C[-4],0))

in all cels going down one column for only the rows that are not empty
or until the cell in coumn a that says End.

What I have right now is this:

Range("E2").Select
ActiveCell.FormulaR1C1 =
"=ISNUMBER(MATCH(RC[-3],ITPatches!C[-4],0))"
Range("F2").Select
ActiveCell.FormulaR1C1 =
"=ISNUMBER(MATCH(RC[-4],ITPatches!C[-4],0))"
Range("G2").Select
ActiveCell.FormulaR1C1 =
"=ISNUMBER(MATCH(RC[-5],ITPatches!C[-4],0))"
Range("E2:G2").Select
Selection.AutoFill Destination:=Range("E2:G5000"),
Type:=xlFillCopy
Range("E2:G5000").Select
ActiveWindow.ScrollRow = 4995
ActiveWindow.ScrollRow = 4989
ActiveWindow.ScrollRow = 4983
ActiveWindow.ScrollRow = 4977
ActiveWindow.ScrollRow = 4965
ActiveWindow.ScrollRow = 4941
ActiveWindow.ScrollRow = 4916
ActiveWindow.ScrollRow = 4886
ActiveWindow.ScrollRow = 4862
ActiveWindow.ScrollRow = 4814
ActiveWindow.ScrollRow = 4765
ActiveWindow.ScrollRow = 4711
ActiveWindow.ScrollRow = 4663
ActiveWindow.ScrollRow = 4614
ActiveWindow.ScrollRow = 4560
ActiveWindow.ScrollRow = 4445
ActiveWindow.ScrollRow = 4391
ActiveWindow.ScrollRow = 4324
ActiveWindow.ScrollRow = 4264
ActiveWindow.ScrollRow = 4197
ActiveWindow.ScrollRow = 4070
ActiveWindow.ScrollRow = 4004
ActiveWindow.ScrollRow = 3949
ActiveWindow.ScrollRow = 3834
ActiveWindow.ScrollRow = 3780
ActiveWindow.ScrollRow = 3738
ActiveWindow.ScrollRow = 3695
ActiveWindow.ScrollRow = 3647
ActiveWindow.ScrollRow = 3550
ActiveWindow.ScrollRow = 3508
ActiveWindow.ScrollRow = 3441
ActiveWindow.ScrollRow = 3393
ActiveWindow.ScrollRow = 3339
ActiveWindow.ScrollRow = 3218
ActiveWindow.ScrollRow = 3145
ActiveWindow.ScrollRow = 3073
ActiveWindow.ScrollRow = 2922
ActiveWindow.ScrollRow = 2843
ActiveWindow.ScrollRow = 2758
ActiveWindow.ScrollRow = 2680
ActiveWindow.ScrollRow = 2601
ActiveWindow.ScrollRow = 2529
ActiveWindow.ScrollRow = 2366
ActiveWindow.ScrollRow = 2293
ActiveWindow.ScrollRow = 2214
ActiveWindow.ScrollRow = 2069
ActiveWindow.ScrollRow = 1997
ActiveWindow.ScrollRow = 1942
ActiveWindow.ScrollRow = 1821
ActiveWindow.ScrollRow = 1755
ActiveWindow.ScrollRow = 1695
ActiveWindow.ScrollRow = 1634
ActiveWindow.ScrollRow = 1580
ActiveWindow.ScrollRow = 1525
ActiveWindow.ScrollRow = 1477
ActiveWindow.ScrollRow = 1368
ActiveWindow.ScrollRow = 1320
ActiveWindow.ScrollRow = 1271
ActiveWindow.ScrollRow = 1235
ActiveWindow.ScrollRow = 1187
ActiveWindow.ScrollRow = 1144
ActiveWindow.ScrollRow = 1102
ActiveWindow.ScrollRow = 1072
ActiveWindow.ScrollRow = 1036
ActiveWindow.ScrollRow = 999
ActiveWindow.ScrollRow = 963
ActiveWindow.ScrollRow = 921
ActiveWindow.ScrollRow = 891
ActiveWindow.ScrollRow = 854
ActiveWindow.ScrollRow = 818
ActiveWindow.ScrollRow = 788
ActiveWindow.ScrollRow = 758
ActiveWindow.ScrollRow = 721
ActiveWindow.ScrollRow = 697
ActiveWindow.ScrollRow = 673
ActiveWindow.ScrollRow = 649
ActiveWindow.ScrollRow = 619
ActiveWindow.ScrollRow = 594
ActiveWindow.ScrollRow = 570
ActiveWindow.ScrollRow = 552
ActiveWindow.ScrollRow = 528
ActiveWindow.ScrollRow = 510
ActiveWindow.ScrollRow = 486
ActiveWindow.ScrollRow = 461
ActiveWindow.ScrollRow = 443
ActiveWindow.ScrollRow = 419
ActiveWindow.ScrollRow = 401
ActiveWindow.ScrollRow = 383
ActiveWindow.ScrollRow = 371
ActiveWindow.ScrollRow = 353
ActiveWindow.ScrollRow = 334
ActiveWindow.ScrollRow = 322
ActiveWindow.ScrollRow = 310
ActiveWindow.ScrollRow = 298
ActiveWindow.ScrollRow = 280
ActiveWindow.ScrollRow = 268
ActiveWindow.ScrollRow = 262
ActiveWindow.ScrollRow = 250
ActiveWindow.ScrollRow = 244
ActiveWindow.ScrollRow = 226
ActiveWindow.ScrollRow = 214
ActiveWindow.ScrollRow = 208
ActiveWindow.ScrollRow = 195
ActiveWindow.ScrollRow = 183
ActiveWindow.ScrollRow = 171
ActiveWindow.ScrollRow = 165
ActiveWindow.ScrollRow = 159
ActiveWindow.ScrollRow = 153
ActiveWindow.ScrollRow = 135
ActiveWindow.ScrollRow = 129
ActiveWindow.ScrollRow = 123
ActiveWindow.ScrollRow = 117
ActiveWindow.ScrollRow = 105
ActiveWindow.ScrollRow = 99
ActiveWindow.ScrollRow = 93
ActiveWindow.ScrollRow = 87
ActiveWindow.ScrollRow = 81
ActiveWindow.ScrollRow = 75
ActiveWindow.ScrollRow = 68
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 50
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 38
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 20
ActiveWindow.ScrollRow = 14
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 2
Range("A1").Select

What I want is to jnot have to do it 5000 rows, but do it only for the
rows that are not a blank cell in coumn A. Or ic can be written until
the cell in column a that sats End. For I add an the word End at the
end of the txt file excel is opening.

*2)* I found this next code from a link in this forum but i would like
to change it around a bit:

Sub Delete_Row()
Dim Firstrow As Long
Dim Lastrow As Long
Dim Lrow As Long
Dim CalcMode As Long

With Application
CalcMode = .Calculation
Calculation = xlCalculationManual
ScreenUpdating = False
End With

Firstrow = ActiveSheet.UsedRange.Cells(1).Row
Lastrow = ActiveSheet.UsedRange.Rows.Count + Firstrow - 1
With ActiveSheet
DisplayPageBreaks = False
For Lrow = Lastrow To Firstrow Step -1
If IsError(.Cells(Lrow, "A").Value) Then
'Do nothing, This avoid a error if there is a error in
the cell

ElseIf .Cells(Lrow, "A").Value = "" Then
Rows(Lrow).Delete
'This will delete each row with the Value "" in Column
A, case sensitive.

End If
Next
End With
With Application
ScreenUpdating = True
Calculation = CalcMode
End With
End Sub

I have changed this to delete any rows that are blank, But I think it
is taking so long to run because it is deleteing all the rows after my
data also, all the way down to 65,000 whatever.

The last row in my daya, once again, has the word end in Column A. Is
there a way to tell this to stop dleteing emtpy rows once it hits the
word End?

(I thank you all for your help and promise that I am slowly learning
this when I find time here at work, going over 5 books of VB and such.
And soon will have my work pay for me to go get a certification in
this. But I thank you for helping me while i am still in my puppy
state)


---
Message posted from http://www.ExcelForum.com/



Reply With QuoteReply With Quote