View Full Version : Copy Cells with Range(Cells R,C). match returning location in numbers
jcramb
10-16-2017, 03:18 PM
I'm using a the match command to find a column and a row. Both are coming back as numbers.
Then i want to use the the row and column numbers to copy cell into a changing amount of cells below to another location in the workbook.
I get error on the bottom line...
Sub CALENDAR2()
Dim CALENDAR As String
Dim CALENDAR_COL As Integer
Dim CALENDAR_DATE_ROWNUM As Integer
Dim ROWCOUNT As Single
Dim DURATION_ORIGINAL As Integer
Dim START_EARLY As Date
Dim FINISH_EARLY As Date
Dim x As Range
ROWCOUNT = 2
CALENDAR = Sheets("Resource Assignments").Range("V" & ROWCOUNT).Value
START_EARLY = Sheets("Resource Assignments").Range("L" & ROWCOUNT).Value
FINISH_EARLY = Sheets("Resource Assignments").Range("M" & ROWCOUNT).Value
DURATION_ORIGINAL = Sheets("Resource Assignments").Range("Q" & ROWCOUNT).Value
'Find Calendar Column in Calendar List
CALENDAR_COL = WorksheetFunction.Match(CALENDAR, Sheets("Calendar List").Range("1:1"), 0)
'Find Calendar Start Date Row Number
CALENDAR_DATE_ROWNUM = WorksheetFunction.Match(CLng(CDate(START_EARLY)), Sheets("Calendar List").Columns(CALENDAR_COL), 0)
Sheets("Calendar List").Range(Cells(CALENDAR_DATE_ROWNUM, CALENDAR_COL), Cells(CALENDAR_DATE_ROWNUM + DURATION_ORIGINAL - 1, CALENDAR_COL)).Copy
Bob Phillips
10-17-2017, 02:36 AM
Is this any better
Sub CALENDAR2()
Dim CALENDAR As String
Dim CALENDAR_COL As Integer
Dim CALENDAR_DATE_ROWNUM As Integer
Dim ROWCOUNT As Single
Dim DURATION_ORIGINAL As Integer
Dim START_EARLY As Date
Dim FINISH_EARLY As Date
Dim x As Range
ROWCOUNT = 2
CALENDAR = Sheets("Resource Assignments").Range("V" & ROWCOUNT).Value
START_EARLY = Sheets("Resource Assignments").Range("L" & ROWCOUNT).Value
FINISH_EARLY = Sheets("Resource Assignments").Range("M" & ROWCOUNT).Value
DURATION_ORIGINAL = Sheets("Resource Assignments").Range("Q" & ROWCOUNT).Value
With Worksheets("Calendar List")
'Find Calendar Column in Calendar List
CALENDAR_COL = WorksheetFunction.Match(CALENDAR, .Range("1:1"), 0)
'Find Calendar Start Date Row Number
CALENDAR_DATE_ROWNUM = WorksheetFunction.Match(CLng(CDate(START_EARLY)), .Columns(CALENDAR_COL), 0)
.Range(.Cells(CALENDAR_DATE_ROWNUM, CALENDAR_COL), .Cells(CALENDAR_DATE_ROWNUM + DURATION_ORIGINAL - 1, CALENDAR_COL)).Copy
End With
jcramb
10-17-2017, 06:35 AM
Is this any better
Sub CALENDAR2()
Dim CALENDAR As String
Dim CALENDAR_COL As Integer
Dim CALENDAR_DATE_ROWNUM As Integer
Dim ROWCOUNT As Single
Dim DURATION_ORIGINAL As Integer
Dim START_EARLY As Date
Dim FINISH_EARLY As Date
Dim x As Range
ROWCOUNT = 2
CALENDAR = Sheets("Resource Assignments").Range("V" & ROWCOUNT).Value
START_EARLY = Sheets("Resource Assignments").Range("L" & ROWCOUNT).Value
FINISH_EARLY = Sheets("Resource Assignments").Range("M" & ROWCOUNT).Value
DURATION_ORIGINAL = Sheets("Resource Assignments").Range("Q" & ROWCOUNT).Value
With Worksheets("Calendar List")
'Find Calendar Column in Calendar List
CALENDAR_COL = WorksheetFunction.Match(CALENDAR, .Range("1:1"), 0)
'Find Calendar Start Date Row Number
CALENDAR_DATE_ROWNUM = WorksheetFunction.Match(CLng(CDate(START_EARLY)), .Columns(CALENDAR_COL), 0)
.Range(.Cells(CALENDAR_DATE_ROWNUM, CALENDAR_COL), .Cells(CALENDAR_DATE_ROWNUM + DURATION_ORIGINAL - 1, CALENDAR_COL)).Copy
End With
jcramb
10-17-2017, 07:49 AM
Is this any better
Sub CALENDAR2()
Dim CALENDAR As String
Dim CALENDAR_COL As Integer
Dim CALENDAR_DATE_ROWNUM As Integer
Dim ROWCOUNT As Single
Dim DURATION_ORIGINAL As Integer
Dim START_EARLY As Date
Dim FINISH_EARLY As Date
Dim x As Range
ROWCOUNT = 2
CALENDAR = Sheets("Resource Assignments").Range("V" & ROWCOUNT).Value
START_EARLY = Sheets("Resource Assignments").Range("L" & ROWCOUNT).Value
FINISH_EARLY = Sheets("Resource Assignments").Range("M" & ROWCOUNT).Value
DURATION_ORIGINAL = Sheets("Resource Assignments").Range("Q" & ROWCOUNT).Value
With Worksheets("Calendar List")
'Find Calendar Column in Calendar List
CALENDAR_COL = WorksheetFunction.Match(CALENDAR, .Range("1:1"), 0)
'Find Calendar Start Date Row Number
CALENDAR_DATE_ROWNUM = WorksheetFunction.Match(CLng(CDate(START_EARLY)), .Columns(CALENDAR_COL), 0)
.Range(.Cells(CALENDAR_DATE_ROWNUM, CALENDAR_COL), .Cells(CALENDAR_DATE_ROWNUM + DURATION_ORIGINAL - 1, CALENDAR_COL)).Copy
End With
jcramb
10-17-2017, 07:56 AM
These two lines of code are working for me. I can locate the column and row of the cell in the table i am looking for.
'Find Calendar Column in Calendar ListCALENDAR_COL = WorksheetFunction.Match(CALENDAR, Sheets("Calendar List").Range("2:2"), 0)
'Find Calendar Start Date Row Number
CALENDAR_DATE_ROWNUM = WorksheetFunction.Match(CALENDAR, Sheets("Calendar List").Columns(CALENDAR_COL), 0)
This is the line of code that is not working. I want to copy this cell and a changing number of cells below to a new location. The number of cell to copy is defined by DURATION_ORIGINAL. I might be thinking about this wrong. Is there a quick way to convert Cell(R,C) to Range(A1)?
.Range(.Cells(CALENDAR_DATE_ROWNUM, CALENDAR_COL), .Cells(CALENDAR_DATE_ROWNUM + DURATION_ORIGINAL - 1, CALENDAR_COL)).Copy
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.