MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Copying and Pasting Cells


Posted by CJ on May 30, 2001 11:19 AM

I am trying to insert a formula into the columns "PPOSAVINGS%", "PPOSAVINGS", and "PPOSTDREDUCT" and have it copied down to the last value. I have the formula inserted and copied down, but it copies all the way to the end of the worksheet. This is a report that will be run often, and the number of rows will vary each time. I am stumped as to how to find a way to get it to copy the formula just to the end of the data (I think for this data set it is like 1400 rows). ANy help is greatly appreciated!!! Thanks!

Private Sub cmdImport_Click()
Dim rec As Recordset
Dim rge As Range
Dim intRows As Integer
Dim intFields As Integer
Dim strSelect As String
Dim strConn As String
Dim db As Database
Dim wsp As Workspace
Dim stDocName1 As String
Dim lRow As Long

lRow = Range("B1").End(xlDown).Row

Call Clear_DataRange

Set wsp = DBEngine.Workspaces(0)
Set db = wsp.OpenDatabase("n:\PPORevenue1.mdb")
db.QueryTimeout = 15000

Set rge = Worksheets("RTF").Range("a7")

Set rec = db.OpenRecordset("Monthly All PPO Results by Processing Site")
rec.MoveLast
intRows = rec.RecordCount
rec.MoveFirst
intFields = rec.Fields.Count

'pastes field names
For intCount1 = 0 To intFields - 1 'do as many times as there are fields
rge.Cells(1, intCount1 + 1).Value = rec.Fields(intCount1).Name
Next intCount1

'pastes field values
For intcount2 = 0 To intRows - 1 'do this as many times as there are rows
For intcount3 = 0 To intFields - 1 'do this as many times as there are fields
rge.Cells(intcount2 + 2, intcount3 + 1).Value = rec.Fields(intcount3).Value
Next intcount3
rec.MoveNext
Next intcount2

'Insert blank columns
Columns("I:K").EntireColumn.Insert
[K7] = "PPOSAVINGS%"
[J7] = "PPOSAVINGS"
[I7] = "PPOSTDREDUCT"

Range("B8").EntireColumn.Insert

'Insert values into columns
Range("J8:J" & Range("B65536").End(xlDown).Row).Formula = "=RC[-1]-RC[3]"

rec.Close


'Format width of columns and add border around titles
Range("A:Z").Columns.AutoFit 'starting and ending cell in ()


'With Worksheets("RTF")
'.Range(.Cells(7, 1), .Cells(7, 7)).Borders.Weight = xlThin
'End With

db.Close
End Sub


Posted by Barrie Davidson on May 30, 2001 11:40 AM

Replace:
Range("J8:J" & Range("B65536").End(xlDown).Row).Formula = "=RC[-1]-RC[3]"

With:
Range("J8:J" & lRow).Formula = "=RC[-1]-RC[3]"

You defined lRow with the statement:
lRow = Range("B1").End(xlDown).Row

I assume that if you go to cell B1 and press END+ARROW DOWN you will get to the last row of your data.

Try it and let me know.

Barrie

Posted by CJ on May 30, 2001 1:38 PM

Thanks Barrie - it does paste the formula, but it pastes it all the way down to row 65,536, even though there is only data until row 1485. When I hit ctrl and the down arrow, it goes all teh way to 65,536... any ideas how to stop that???

Posted by Barrie Davidson on May 30, 2001 1:57 PM


CJ, what column contains data in ALL cells (from the first row to the bottom row)?

Barrie

Posted by CJ on May 30, 2001 2:20 PM

Barrie, column a has data from row 1 all the way to 1485. : Thanks Barrie - it does paste the formula, but it pastes it all the way down to row 65,536, even though there is only data until row 1485. When I hit ctrl and the down arrow, it goes all teh way to 65,536... any ideas how to stop that???

Posted by Barrie Davidson on May 30, 2001 2:37 PM

Perfect. Let's try this again.

Replace:
Range("J8:J" & Range("B65536").End(xlDown).Row).Formula = "=RC[-1]-RC[3]"

With:
Range("J8:J" & lRow).Formula = "=RC[-1]-RC[3]"

Replace:
lRow = Range("B1").End(xlDown).Row

With:
lRow = Range("A1").End(xlDown).Row

Try it again and let me know.

Regards,
Barrie

Posted by CJ on May 30, 2001 3:02 PM

Hi again Barrie, it is still copying all the way to the bottom. My code is below. I don't know if this is what the problem is, but when I delete all the data, so all I have in the spreadsheet is the button that calls the procedure, If I do the control end, it goes to the row 65536, even though there is no data at all in the spreadsheet. Could this be the reason why???

Thanks again,
CJ

Private Sub cmdImport_Click()
Dim rec As Recordset
Dim rge As Range
Dim intRows As Integer
Dim intFields As Integer
Dim strSelect As String
Dim strConn As String
Dim db As Database
Dim wsp As Workspace
Dim stDocName1 As String
Dim lRow As Long
Dim RangeStr As String

lRow = Range("A1").End(xlDown).Row

Call Clear_DataRange

Set wsp = DBEngine.Workspaces(0)
Set db = wsp.OpenDatabase("n:\PPORevenue1.mdb")
db.QueryTimeout = 15000

Set rge = Worksheets("RTF").Range("a7")

Set rec = db.OpenRecordset("Monthly All PPO Results by Processing Site")
rec.MoveLast
intRows = rec.RecordCount
rec.MoveFirst
intFields = rec.Fields.Count

'pastes field names
For intCount1 = 0 To intFields - 1 'do as many times as there are fields
rge.Cells(1, intCount1 + 1).Value = rec.Fields(intCount1).Name
Next intCount1

'pastes field values
For intcount2 = 0 To intRows - 1 'do this as many times as there are rows
For intcount3 = 0 To intFields - 1 'do this as many times as there are fields
rge.Cells(intcount2 + 2, intcount3 + 1).Value = rec.Fields(intcount3).Value
Next intcount3
rec.MoveNext
Next intcount2

'Insert blank columns
Columns("I:K").EntireColumn.Insert
[K7] = "PPOSAVINGS%"
[J7] = "PPOSAVINGS"
[I7] = "PPOSTDREDUCT"

RangeStr = "J" & (7 + RecordCount)
Range("B8").EntireColumn.Insert

'Insert values into columns
'Range("J8:J" & Range("B65536").End(xlUp).Row).Formula = "=RC[-1]-RC[3]"
'Range("J8:J" & Range("RangeStr").End(xlUp).Row).Formula = "=RC[-1]-RC[3]"
Range("J8:J" & lRow).Formula = "=RC[-1]-RC[3]"
Range("K8:K" & lRow).Formula = "=RC[2]-RC[3]"
Range("L8:L" & lRow).Formula = "=RC[-1]/RC[-3]"
rec.Close


'Format width of columns and add border around titles
Range("A:Z").Columns.AutoFit 'starting and ending cell in ()


'With Worksheets("RTF")
'.Range(.Cells(7, 1), .Cells(7, 7)).Borders.Weight = xlThin
'End With

db.Close
End Sub

Posted by Barrie Davidson on May 30, 2001 5:19 PM

Hi CJ, you are bang on with your guess. If I understand your code, try putting:
lRow = 7 + RecordCount
right before:
RangeStr = "J" & (7 + RecordCount)
and delete:
lRow = Range("A1").End(xlDown).Row


Let me know if this works for you.
Barrie
PS - I won't be checking MrExcel until Saturday.

Posted by CJ on May 31, 2001 12:19 PM

Thanks Barrie!!!

I finally got it to work - thanks so much for all your help!!! :) Hi CJ, you are bang on with your guess. If I understand your code, try putting: