Click here for important information about Computing.net.

Solved Resizing Range from Within a For Loop

Microsoft / Office 2013; 365 32/64-bi...
December 16, 2020 at 22:26:54
Specs: Windows 10 x64, i5-8265U / 8 GB
Hello,

I am struggling with changing the first row of a range from within a For loop. I tried thinking of other ways to do it, but I can't think of any.

I am working with SAP here and it will only let me enter data in a number of visible rows (starting from row 0 to DataRows - 1) at a time, then once I scroll down, row numbering starts again from 0 to DataRows - 1 and so on until I loop through my whole range.

The problem is I need to somehow change the range from within the For loop, or do it in another way I can't figure out.

The code below is working (all variables properly declared) up until the point I have to change Rng FirstRow, as of course it won't change my Rng from within an already running loop on the Rng.

Here's my snippet:

LastRow = ws.Range("B:B").Find(what:="Stoc Tip A", after:=ws.Range("B31"), _
                searchdirection:=xlPrevious).Row
LastRow = LastRow - 6
FirstRow = 32 'always
Set Rng = ws.Range("B" & FirstRow & ":B" & LastRow).SpecialCells(xlCellTypeVisible)

For Each cell In Rng
    Set Rng = ws.Range("B" & FirstRow & ":B" & LastRow).SpecialCells(xlCellTypeVisible)
    NumRow = 0
    'Set Rng = Rng.Resize(RngRows - DataRows)
    RngRows = Rng.Rows.Count

        For NumRow = 0 To DataRows - 1 Step 1
            Mat = cell.Offset(NumRow, 0).Value
            Cant = cell.Offset(NumRow, 3).Value
            Val = cell.Offset(NumRow, 4).Value
            SAP_session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\" & _
                "01/ssubSUBSCREEN_BODY:SAPMV45A:4400/subSUBSCREEN_TC:SAPMV45A:4900/" & _
                "tblSAPMV45ATCTRL_U_ERF_AUFTRAG/ctxtRV45A-MABNR[1," & NumRow & "]").Text = Mat
            SAP_session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\" & _
                "01/ssubSUBSCREEN_BODY:SAPMV45A:4400/subSUBSCREEN_TC:SAPMV45A:4900/" & _
                "tblSAPMV45ATCTRL_U_ERF_AUFTRAG/txtRV45A-KWMENG[2," & NumRow & "]").Text = Cant
            SAP_session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\" & _
                "01/ssubSUBSCREEN_BODY:SAPMV45A:4400/subSUBSCREEN_TC:SAPMV45A:4900/" & _
                "tblSAPMV45ATCTRL_U_ERF_AUFTRAG/txtKOMV-KBETR[15," & NumRow & "]").Text = Val
        Next
        
    SAP_session.findById("wnd[0]").sendVKey 0
    SAP_session.findById("wnd[0]/usr/tabsTAXI_TABSTRIP_OVERVIEW/tabpT\" & _
        "01/ssubSUBSCREEN_BODY:SAPMV45A:4400/subSUBSCREEN_TC:SAPMV45A:4900/" & _
        "tblSAPMV45ATCTRL_U_ERF_AUFTRAG").verticalScrollbar.Position = DataRows
    FirstRow = Rng.Row + DataRows
    'Set Rng = ws.Range("B" & FirstRow & ":B" & LastRow).SpecialCells(xlCellTypeVisible)
Next

Any suggestions?

message edited by Mrrrr


See More: Resizing Range from Within a For Loop


#1
December 17, 2020 at 13:24:27
I don't use SAP, so your comments about where it will let you enter data, scrolling, etc. don't mean anything to me as far as the macro goes. Therefore, it's hard for me to understand exactly what your code is trying to do. I don't know really what part works and what part doesn't. I also don't know what you mean when you say it "works" up until the point you change the FirstRow variable. None of this is a reflection on you or your code, because as I said, I don't use SAP so I can't visualize your issue.

In addition, I see that you set the variable RngRows but you don't ever use it. Was that just something left over from a test? I see it used in an instruction that is commented out, but that instruction comes before the variable is even set. Confusing, to say the least. It's hard to tell what is actual code and what is just stuff left over from your testing.

In any case, let me toss this out, just to see if it gives you any ideas. Of course, I could be way off base here. ;-)

Instead of trying to change the Range inside the loop, would it be possible for the loop to work on a range of a specific size over and over again? Here's what I mean:

Let's say that SAP allows you enter data in groups of 10 rows at a time. This code will work on 10 rows at a time then reset FirstRow and do another 10 rows, until LastRow has been reached. Since I don't understand the "scroll" issue with your data entry, maybe you could add some code to scroll the page as necessary.

Sub LoopGroupsOf10()
'Set First and Last Row

 FirstRow = 32 'always
 LastRow = ws.Range("B:B").Find(what:="Stoc Tip A", after:=ws.Range("B31"), _
                searchdirection:=xlPrevious).Row
 LastRow = LastRow - 6

'Do SAP stuff on 10 rows at a time until LastRow exceeded
  Do Until rw > LastRow
     For rw = FirstRow To FirstRow + 9
     
'Stop doing SAP stuff if LastRow reached
         If rw > LastRow Then Exit For
         
'***** Do your SAP stuff Here*****
     Next
  
'Increment FirstRow by 10
       FirstRow = FirstRow + 10
       
'Do next group of rows
  Loop
  
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#2
December 18, 2020 at 00:02:15
Thank you for the answer and the time it took to write all that. ;-)

I know I can't explain well since in my head I understand it. Wrote it then rewrote it trying to explain better, then edited a few times. Thought it was just something I was missing and that would easily be spotted by a VBA expert like yourself and others.

I tried using your logic into my code, but the thing is I also need to loop on a range of visible cells only (eg 27 visible rows out of 6000+). And committing to that range is the thing that prevents me from changing the FirstRow in my code above.

Instead of posting my code with SAP lines, below I will try to simplify my logic as much as possible and explain myself as well as I can. Sorry if I am still using words that are maybe inappropriate in the context or in relation to Excel and VBA, I'm not a native English speaker nor a native VBA and Excel speaker, as you've noticed :-).

So:

- In Sheet1 I have a table starting from row 32 to row 6836
- That table is filtered to, let's say, 27 visible rows (my Rng is the 27 visible cells in column B)
- FirstRow (cell 1) of Rng is B33, LastRow (cell 27) of Rng is B6005
- I need to loop through Rng, 4 rows at a time and copy the data of each row to another sheet (let's call it Sheet2)
- On Sheet2, the 4 destination rows each have a number (RowNum) starting from 0 to 3
- to clarify, RowNum is 4 for this Rng loop, won't change during the same "run" on Rng (it may be different from 4 on a different monitor or laptop (8, 13 etc.), but on the same screen it remains the same). RowNum changes accordingly from 0-3 to 0-7, 0-12 etc.
- So data from cell 1 of Rng (FirstRow) goes in Sheet2 - RowNum 0, data from cell 2 of Rng goes to RowNum 1, cell 3 goes in RowNum 2 and cell 4 goes in RowNum 3 (RowNum is a must in my SAP formula as to insert data in there I need to address SAP row by number)
- After reaching cell 4 of Rng with data inserted in RowNum 3 on Sheet2, I need to change FirstRow from cell 1 of Rng to cell 5 of Rng
- Then I need to copy data from cells 5-8 of Rng to the same RowNum 0 to 3 of Sheet 2
- And so on until I do it for all 27 rows of Rng

I hope this is better.

message edited by Mrrrr


Reply ↓  Report •

#3
December 18, 2020 at 09:52:46
✔ Best Answer
I don't have a lot of time to test much, and I'll probably be away from Excel for at least the next week, but I'll give this another try.

From your first three bullet items, it appears that your Visible cells are not contiguous. e.g. Rng might be B33, B135, B4529 ,B6005.

What if you were loop through the entire table (B32:B6836) and save each set of 4 visibly-contiguous Row numbers in an array. Once the 4 row array was built, you do your SAP stuff on that 4 row Rng and then build another 4 row Rng.

The following code finds 4 visibly-contiguous rows and builds a Rng with those cells.

There is one problem with this method that I'll leave for you to figure out: 27 is not divisible by 4, therefore the last attempt at building a 4 cell Rng results in a string that cannot be used to Set the Rng.

i.e. Instead of getting a string that looks like "B4195, B5050, B6001, B6005", you might get something like "B6254, B6836, B0, B0" because the For-Next loop stopped adding rows to Array once it reached 6836. When the Set instruction tries to Set that Rng, it fails.

(There could be other issues, but that's all I have time to test and of course, I'm not testing it against your data.)

I hope this gives you some ideas...

Sub GroupVisibleRows()
Dim visRows(4) As Integer

'Initiate Visible Row counter & First Row
 visRow = 0
 FirstRow = 32
 LastRow = 6836
 
'Do SAP stuff on 4 rows at a time until LastRow exceeded
  Do
  
'Test until array contains 4 Visible rows
    For rw = FirstRow To LastRow
      If Not Rows(rw).Hidden Then
        visRows(visRow) = rw
        visRow = visRow + 1
     If visRow = 4 Then Exit For
     End If
    Next
  
'Build tmpRng address from array elements
    For vis = 0 To 3
      tmpRng = tmpRng & "B" & visRows(vis) & ","
    Next
    
    MsgBox tmpRng 'Display for testing
    
'Set Rng, Stripping extra comma from string
    Set Rng = Range(Left(tmpRng, Len(tmpRng) - 1))
    Rng.Select 'Selected for Testing ***** Do your SAP stuff on that range ****

'Increment FirstRow variable, Clear array and other variables
   FirstRow = rw + 1
   tmpRng = ""
   visRow = 0
   Erase visRows
   
  Loop
  
End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

Related Solutions

#4
December 23, 2020 at 03:43:40
Thanks, will try using the array!

I will be able to test extensively only in 2021 so...

Happy Holidays! ;-)


Reply ↓  Report •

Ask Question