Andim
07-16-2008, 08:41 AM
Hi everyone
I have a piece of code that I have put together myself (with help as it is my first). It is a command button that will take the highest number from a range of cells and put this into a variable then it will take a reference from another cell relating to the range. The idea next is to open another spreadsheet and find the match of this reference and offset along 2 cells and paste the highest number. Here is the code -
Private Sub CommandButton3_Click()
lm = ActiveWorkbook.Name
Dim ref As Range, gethighest As Range
Set ref = Worksheets("Sheet1").Range("A19")
Set gethighest = Worksheets("Sheet1").Range("A20:C23")
highest = Application.WorksheetFunction.Max(gethighest)
Workbooks.Open ("C:\Documents and Settings\Andrew\My Documents\Serious Stuff\TCSpreadsheets\Convoluted.xls")
cd = ActiveWorkbook.Name
Dim cdrefcol As Range
Dim cellad As Range
Dim Cell As Object
Set cdrefcol = Worksheets("Sheet1").Range("A10:A12")
For Each Cell In cdrefcol
If Cell.Value = ref Then
If cellad Is Nothing Then
Set cellad = Range(Cell.Address)
Else
Set cellad = Union(cellad, Range(Cell.Address))
End If
End If
Next
cellad.Offset(0, 2).Value = highest
Workbooks(cd).Close SaveChanges:=True
End Sub
All of the code does what I want it to except the line in red. When it comes to pasting the highestnumbe in the correct cell it does it but on the wrong sheet (the first sheet lm instead of the second cd). I know it will be something simple but I just can't figure out why it is doing this. Any help will be very much appreciated.
Cheers
Andim
I have a piece of code that I have put together myself (with help as it is my first). It is a command button that will take the highest number from a range of cells and put this into a variable then it will take a reference from another cell relating to the range. The idea next is to open another spreadsheet and find the match of this reference and offset along 2 cells and paste the highest number. Here is the code -
Private Sub CommandButton3_Click()
lm = ActiveWorkbook.Name
Dim ref As Range, gethighest As Range
Set ref = Worksheets("Sheet1").Range("A19")
Set gethighest = Worksheets("Sheet1").Range("A20:C23")
highest = Application.WorksheetFunction.Max(gethighest)
Workbooks.Open ("C:\Documents and Settings\Andrew\My Documents\Serious Stuff\TCSpreadsheets\Convoluted.xls")
cd = ActiveWorkbook.Name
Dim cdrefcol As Range
Dim cellad As Range
Dim Cell As Object
Set cdrefcol = Worksheets("Sheet1").Range("A10:A12")
For Each Cell In cdrefcol
If Cell.Value = ref Then
If cellad Is Nothing Then
Set cellad = Range(Cell.Address)
Else
Set cellad = Union(cellad, Range(Cell.Address))
End If
End If
Next
cellad.Offset(0, 2).Value = highest
Workbooks(cd).Close SaveChanges:=True
End Sub
All of the code does what I want it to except the line in red. When it comes to pasting the highestnumbe in the correct cell it does it but on the wrong sheet (the first sheet lm instead of the second cd). I know it will be something simple but I just can't figure out why it is doing this. Any help will be very much appreciated.
Cheers
Andim