Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB, Vlookup worksheet function and copying data
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VB, Vlookup worksheet function and copying data
On Jul 22, 2:34*pm, AllyB wrote:
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 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying rows of data into new worksheet but placing data into colu | Excel Discussion (Misc queries) | |||
Copying data from one worksheet to another. | Excel Programming | |||
Using Vlookup worksheet function in VBA | Excel Programming | |||
Worksheet function Vlookup | Excel Programming | |||
Vlookup worksheet function | Excel Worksheet Functions |