VB, Vlookup worksheet function and copying data

[AllyB]

Hello,

I have a spreadsheet that lists deliverables and based on a y/n creates a
second sheet to track the necessary approvals. Deliverables are associated
with a project phase, and must be sorted in order of phase (but within a
phase no sorting is necessary). Once the approvals sheet is created, cells
must be populated with NA if a role does not need to approve a deliverable.
As approvals are granted, the date must be populated in the appropriate cell.
This all works wonderfully as long as there are no updates to be done. It
is entirely possible, however, that a new deliverable can be identified and
the approvals sheet be updated to include this new deliverable in the
appropriate phase. I've been able to get the NA recreation code to work, but
cannot get the dates that have already been input to copy with the
appropriate deliverable. My approach has been to copy the current approval
range (cells B11 to Q132) to AB11:AQ32 and then do a loop to look at the
value in the current row of B, find that in the first column of the backup
range (AB11:AQ132) and then copy the range of AH##:AQ##, where ## is the row
in which the copy resides, to the range of H%%:Q%% where %% is the row of the
value in B we are looking up. The code I've done to date is noted below but
doesn't work. Any and all help on this will be greatly, greatly
appreciated!!!



Private Sub RecoverDates()

Dim strCurrCell As String
Dim intCurRow As Integer
Dim strUpdateCell As Range

Dim strLookupCell As Variant
Dim strUpdateRow As Range
Dim strUpdateRange As Range
Dim strFindRange As Range
Dim strCopyfromRange As Range


intCurRow = 11
For intCurRow = 11 To 90
Set strFindRange = Worksheets("Project Approval
Meetings").Range("$AB$11:$AQ$132")
num = Application.Match("'$B$" & intCurRow & "'",
strFindRange.Columns(1), 0)
Set strUpdateCell = strFindRange(num, 8)
If (Not (strUpdateRange Is Nothing)) Then
strUpdateRow = "$H$" & strUpdateRow & ":$Q$" & strUpdateRow
strCopyfromRange = "$AH$" & strUpdateRow & ":$AQ$" & strUpdateRow
strCopyfromRange.Select
strUpdateRow = strCopyfromRange
End If
Next
End Sub

 

[Matthew Herbert]

Hello,

I have a spreadsheet that lists deliverables and based on a y/n creates a
second sheet to track the necessary approvals.  Deliverables are associated
with a project phase, and must be sorted in order of phase (but within a
phase no sorting is necessary).  Once the approvals sheet is created, cells
must be populated with NA if a role does not need to approve a deliverable.  
As approvals are granted, the date must be populated in the appropriate cell.
 This all works wonderfully as long as there are no updates to be done. It
is entirely possible, however, that a new deliverable can be identified and
the approvals sheet be updated to include this new deliverable in the
appropriate phase.  I've been able to get the NA recreation code to work, but
cannot get the dates that have already been input to copy with the
appropriate deliverable.  My approach has been to copy the current approval
range (cells B11 to Q132) to AB11:AQ32 and then do a loop to look at the
value in the current row of B, find that in the first column of the backup
range (AB11:AQ132) and then copy the range of AH##:AQ##, where ## is the row
in which the copy resides, to the range of H%%:Q%% where %% is the row ofthe
value in B we are looking up.  The code I've done to date is noted below but
doesn't work.  Any and all help on this will be greatly, greatly
appreciated!!!

Private Sub RecoverDates()

Dim strCurrCell As String
Dim intCurRow As Integer
Dim strUpdateCell As Range

Dim strLookupCell As Variant
Dim strUpdateRow As Range
Dim strUpdateRange As Range
Dim strFindRange As Range
Dim strCopyfromRange As Range

    intCurRow = 11
    For intCurRow = 11 To 90
        Set strFindRange = Worksheets("Project Approval
Meetings").Range("$AB$11:$AQ$132")
        num = Application.Match("'$B$" & intCurRow & "'",
strFindRange.Columns(1), 0)
        Set strUpdateCell = strFindRange(num, 8)
        If (Not (strUpdateRange Is Nothing)) Then
            strUpdateRow = "$H$" & strUpdateRow & ":$Q$" & strUpdateRow
            strCopyfromRange = "$AH$" & strUpdateRow & ":$AQ$" & strUpdateRow
            strCopyfromRange.Select
            strUpdateRow = strCopyfromRange
        End If
    Next
End Sub

AllyB,

I think that I follow your explanation, but putting the explanation to
the side and looking at your code, I have a few items to point out for
your consideration.

(1) If you open the Excel function dialog box and search for MATCH,
you'll note that the help file states the following: "Returns the
relative position of an item in an array that matches a specified
value in a specified order." Your num = Application.Match("'$B$" &
intCurRow & "'", strFindRange.Columns(1), 0) line returns a relative
position in the table, but your table appears to start on intCurRow.
As a result, your "num" is probably off by intCurRow - 1 rows.
Dimension your "num" variable as a Variant (see point 2 below).

(2) It looks like you are trying to identify your variable data types
in your variable names; however, you are mixing things up. For
example, you have strUpdateRow As Range and strCurrCell As String. Of
course, you will adopt your own style, but str for String, rng for
Range, var for Variant, int for Integer seems to make more sense in
your code; thus, rngUpdateRow As Range makes more sense than
strUpdateRow. You'll want "num" be a variant because Match can return
an indexed number, starting at 1, or an error. Be sure to test for
the error case (IsError(num) works fine for this).

(3) strFindRange is a range object, so I'm not sure what strFindRange
(num) is in your code. Something like Cells(num, 8) is valid,
strFindRange.Cells(num), or strFindRange.Cells(num,8) is valid.
(There are TONS of ways to work with ranges). Be sure to test your
variable results by stepping line by line through your code (F8
repeatedly), hovering over variables with the mouse, printing values
to the Immediate Window (View | Immediate Window) - Debug.Print
strFindRange.Address, or using other debugging tools.

(4) You dimensioned strUpdateRow, strUpdateRange, and strCopyfromRange
as RANGE, but you aren't creating these variables as objects in your
code (i.e. you Set strFindRange as a valid range object, but not the
others). Variables dimensioned as RANGE need to be created as range
objects via the Set statement if you want them to return an object
that is not Nothing. (For example, strUpdateRow = strCopyfromRange
won't work, but Set strUpdateRow = strFindRange will work).

(5) If you create a string of "range" text, e.g. strAddress = "$AH$" &
strUpdateRow & ":$AQ$" & strUpdateRow then you can select this range
via Range(strAddress).Select, or create a range object as follows: Set
Rng = Range(strAddress).

Start with these fixes and see if this helps to get you closer to your
desired result.

Best,

Matthew Herbert