L
Les Stout
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 ***
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 ***