Hi all, i got the code from J.Walkenbachs book and i need to adopt it to
my code and have been trying all morning !!!
(Now 13:30 here)My problem is that i need to adopt it to my code at the
bottom. The problem being that the spreadsheet is not a fixed lenght and
can be 10 rows or can also be 10000 rows !!
I would really appreciate some help with the code.


Sub Main()
' Inserts random numbers on the active worksheet
Dim Counter As Integer
Dim RowMax As Integer, ColMax As Integer
Dim r As Integer, c As Integer
Dim PctDone As Single

If TypeName(ActiveSheet) < "Worksheet" Then Exit Sub
Cells.Clear
Counter = 1
RowMax = 200
ColMax = 25
For r = 1 To RowMax
For c = 1 To ColMax
Cells(r, c) = Int(Rnd * 1000)
Counter = Counter + 1
Next c
PctDone = Counter / (RowMax * ColMax)
Call UpdateProgress(PctDone)
Next r
Unload UserForm1
End Sub

------------------------------------------------------------------
Sub UpdateProgress(Pct)
With UserForm1
.FrameProgress.Caption = Format(Pct, "0%")
.LabelProgress.Width = Pct * (.FrameProgress.Width - 10)
.Repaint
End With
End Sub
------------------------------------------------------------------

Sub Lookups()

Dim myLookUpRng As Range
Dim i As Long

Application.StatusBar = "Your prices are being compared to the
supplier prices"
Range("D4").Select
With Workbooks(SuppFileNameC).Worksheets(SheetName)
Set myLookUpRng = .Range("D:N")
End With
For i = 4 To Cells(Rows.Count, "D").End(xlUp).Row
Cells(i, "L").Value = Application.VLookUp(Cells(i, "D").Value, _
myLookUpRng, 9, 0)
Cells(i, "L").Value = Cells(i, "L").Value
Cells(i, "M").Value = Application.VLookUp(Cells(i, "D").Value, _
myLookUpRng, 10, 0)
Next i
Range("A4").Select
InsPriceDiff
End Sub



Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Use a simple test to get the lastrow

iLastRow = Cells(Rows.Count,"A").End(xlUp).Row

change A to suit.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Les Stout" wrote in message
...
Hi all, i got the code from J.Walkenbachs book and i need to adopt it to
my code and have been trying all morning !!!
(Now 13:30 here)My problem is that i need to adopt it to my code at the
bottom. The problem being that the spreadsheet is not a fixed lenght and
can be 10 rows or can also be 10000 rows !!
I would really appreciate some help with the code.


Sub Main()
' Inserts random numbers on the active worksheet
Dim Counter As Integer
Dim RowMax As Integer, ColMax As Integer
Dim r As Integer, c As Integer
Dim PctDone As Single

If TypeName(ActiveSheet) < "Worksheet" Then Exit Sub
Cells.Clear
Counter = 1
RowMax = 200
ColMax = 25
For r = 1 To RowMax
For c = 1 To ColMax
Cells(r, c) = Int(Rnd * 1000)
Counter = Counter + 1
Next c
PctDone = Counter / (RowMax * ColMax)
Call UpdateProgress(PctDone)
Next r
Unload UserForm1
End Sub

------------------------------------------------------------------
Sub UpdateProgress(Pct)
With UserForm1
.FrameProgress.Caption = Format(Pct, "0%")
.LabelProgress.Width = Pct * (.FrameProgress.Width - 10)
.Repaint
End With
End Sub
------------------------------------------------------------------

Sub Lookups()

Dim myLookUpRng As Range
Dim i As Long

Application.StatusBar = "Your prices are being compared to the
supplier prices"
Range("D4").Select
With Workbooks(SuppFileNameC).Worksheets(SheetName)
Set myLookUpRng = .Range("D:N")
End With
For i = 4 To Cells(Rows.Count, "D").End(xlUp).Row
Cells(i, "L").Value = Application.VLookUp(Cells(i, "D").Value, _
myLookUpRng, 9, 0)
Cells(i, "L").Value = Cells(i, "L").Value
Cells(i, "M").Value = Application.VLookUp(Cells(i, "D").Value, _
myLookUpRng, 10, 0)
Next i
Range("A4").Select
InsPriceDiff
End Sub



Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

You don't need the Main routine - that is just for demonstration


Sub Lookups()

Dim myLookUpRng As Range
Dim i As Long
Dim NumRows as Long
Dim LastRow as Long

Application.StatusBar = "Your prices are being compared to the
supplier prices"
Range("D4").Select
With Workbooks(SuppFileNameC).Worksheets(SheetName)
Set myLookUpRng = .Range("D:N")
End With
LastRow = Cells(Rows.Count, "D").End(xlUp).Row
NumRows = LastRow - 3
For i = 4 To LastRow
Cells(i, "L").Value = Application.VLookUp(Cells(i, "D").Value, _
myLookUpRng, 9, 0)
Cells(i, "L").Value = Cells(i, "L").Value
Cells(i, "M").Value = Application.VLookUp(Cells(i, "D").Value, _
myLookUpRng, 10, 0)
UpdateProgress (i-3)/NumRows
Next i
Range("A4").Select
InsPriceDiff
End Sub

Is what I believe you need.

--
Regards,
Tom Ogilvy


"Les Stout" wrote in message
...
Hi all, i got the code from J.Walkenbachs book and i need to adopt it to
my code and have been trying all morning !!!
(Now 13:30 here)My problem is that i need to adopt it to my code at the
bottom. The problem being that the spreadsheet is not a fixed lenght and
can be 10 rows or can also be 10000 rows !!
I would really appreciate some help with the code.


Sub Main()
' Inserts random numbers on the active worksheet
Dim Counter As Integer
Dim RowMax As Integer, ColMax As Integer
Dim r As Integer, c As Integer
Dim PctDone As Single

If TypeName(ActiveSheet) < "Worksheet" Then Exit Sub
Cells.Clear
Counter = 1
RowMax = 200
ColMax = 25
For r = 1 To RowMax
For c = 1 To ColMax
Cells(r, c) = Int(Rnd * 1000)
Counter = Counter + 1
Next c
PctDone = Counter / (RowMax * ColMax)
Call UpdateProgress(PctDone)
Next r
Unload UserForm1
End Sub

------------------------------------------------------------------
Sub UpdateProgress(Pct)
With UserForm1
.FrameProgress.Caption = Format(Pct, "0%")
.LabelProgress.Width = Pct * (.FrameProgress.Width - 10)
.Repaint
End With
End Sub
------------------------------------------------------------------

Sub Lookups()

Dim myLookUpRng As Range
Dim i As Long

Application.StatusBar = "Your prices are being compared to the
supplier prices"
Range("D4").Select
With Workbooks(SuppFileNameC).Worksheets(SheetName)
Set myLookUpRng = .Range("D:N")
End With
For i = 4 To Cells(Rows.Count, "D").End(xlUp).Row
Cells(i, "L").Value = Application.VLookUp(Cells(i, "D").Value, _
myLookUpRng, 9, 0)
Cells(i, "L").Value = Cells(i, "L").Value
Cells(i, "M").Value = Application.VLookUp(Cells(i, "D").Value, _
myLookUpRng, 10, 0)
Next i
Range("A4").Select
InsPriceDiff
End Sub



Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Thanks Bob for your input, Tom thanks, your code works 100%.

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Hi Tom, have tried to include the counter in the code below, but i
obviously am hopelessly lost !!
Could you please help again, maybe with a bit of explanation if
possible....Thanks in advance.

Sub InsPriceDiff()
Dim rng As Range, cell As Range
Dim myNum As Variant
Dim i As Long
Dim NumRows As Long
Dim LastRow As Long
Set rng = Range(Range("H4"), Range("H4").End(xlDown))
LastRow = Cells(Rows.Count, "D").End(xlUp).Row
NumRows = LastRow - 3
For Each cell In rng
If cell.Offset(0, 4) < cell.Value And Not
IsEmpty(cell.Offset(0, 4)) Then
myNum = cell.Offset(0, 0) - cell.Offset(0, 4)
cell.Offset(0, 3) = myNum
End If
UpdateProgressDiff (cell - 3) / NumRows
Next
Unload UserForm3
FormatColumnsC
End Sub

-----------------------------------------------------------------

Sub UpdateProgressDiff(Pct)
With UserForm3
.FrameProgress.Caption = Format(Pct, "0%")
.LabelProgress.Width = Pct * (.FrameProgress.Width - 10)
.Repaint
End With
End Sub


Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Sub InsPriceDiff()
Dim rng As Range, cell As Range
Dim myNum As Variant
Dim i As Long
Dim NumRows As Long
Dim LastRow As Long
Dim StartRow As Long
Set rng = Range(Range("H4"), Range("H4").End(xlDown))
' get the last row
LastRow = rng.rows(rng.rows.count).row
' get the first row
StartRow = rng.row
' subtract first from last and add 1
' to get the number of rows. this will
' be the denominator
NumRows = Lastrow - StartRow + 1
For Each cell In rng
If cell.Offset(0, 4) < cell.Value And Not _
IsEmpty(cell.Offset(0, 4)) Then
myNum = cell.Offset(0, 0) - cell.Offset(0, 4)
cell.Offset(0, 3) = myNum
End If
UpdateProgressDiff (cell.Row - Startrow + 1 ) / NumRows
Next
Unload UserForm3
FormatColumnsC
End Sub

--
Regards,
Tom Ogilvy

"Les Stout" wrote in message
...
Hi Tom, have tried to include the counter in the code below, but i
obviously am hopelessly lost !!
Could you please help again, maybe with a bit of explanation if
possible....Thanks in advance.

Sub InsPriceDiff()
Dim rng As Range, cell As Range
Dim myNum As Variant
Dim i As Long
Dim NumRows As Long
Dim LastRow As Long
Set rng = Range(Range("H4"), Range("H4").End(xlDown))
LastRow = Cells(Rows.Count, "D").End(xlUp).Row
NumRows = LastRow - 3
For Each cell In rng
If cell.Offset(0, 4) < cell.Value And Not
IsEmpty(cell.Offset(0, 4)) Then
myNum = cell.Offset(0, 0) - cell.Offset(0, 4)
cell.Offset(0, 3) = myNum
End If
UpdateProgressDiff (cell - 3) / NumRows
Next
Unload UserForm3
FormatColumnsC
End Sub

-----------------------------------------------------------------

Sub UpdateProgressDiff(Pct)
With UserForm3
.FrameProgress.Caption = Format(Pct, "0%")
.LabelProgress.Width = Pct * (.FrameProgress.Width - 10)
.Repaint
End With
End Sub


Les Stout

*** Sent via Developersdex http://www.developersdex.com ***

Thanks a million Tom, great day further im on my way home.

Les Stout

*** Sent via Developersdex http://www.developersdex.com ***