title: Passing a variable in the Range function in Excel VBA and looping it
body: <p>I have a source Excel sheet called Shipper VP. There are names of three persons in the cells B4,C4,D4 of the Shipper VP and corresponding data in the columns. I am extracting their data for each person and creating separate excel tabs for each person and renaming it to their respective name. Plus there are bunch of other calculations.
  This is the code: (everything works)</p>
  
  <pre><code>Sub Test()
      Dim wsSource As Worksheet, ws As Worksheet
      Dim rg As Range, c As Range
      Dim n As Integer 
  
      Set wsSource = Sheets("Shipper VP")
      Set rg = wsSource.Range("B4:D4")
      n = 1
      For Each c In rg
           'add sheet and rename
          Set ws = Sheets.Add
          ws.Name = c.Text
  
           'other calculations
           '...
  
          n = n + 1
      Next c
  End Sub
  </code></pre>
  
  <p>However, at present I know that there are three persons in the Source sheet(ws.Source), I want to loop the same thing for “n” number of persons.
  For each person, it starts from B4, and continues to C4, D4, E4 etc. till the number of persons in the Source Sheet. The last column is for Grand Total but I extract data only till the previous column and not for the Grand Total column.</p>
  
  <p>Now in the above code Instead of ("B4:D4") in the rg range, I want D4 cell to be a variable so that it can be anything like Z4 or AK4 etc. It there are three persons, the cell address it takes from the source sheet (wsSource) is from B4:D4, if there are 10 persons, it will be from B4:K4. If there are 100 persons again it will change to B4:x4, where x will be the (100+1)th  column of the source excel sheet(wsSource).</p>
  
  <p>I am not able to pass this variable to the following code:
     Set rg = wsSource.Range("B4:D4")</p>
  
  <p>So, what it should do if there are "n" persons (which I may not know the number of persons) in the source sheet, there should be a variable which checks until the cell value starting from B4  is null, then in the next code it should automatically set the range for rg = wsSource.Range(“B4:x4”), where x is the (100+1)th column.</p>
  
  <p>But during searching when there are three persons, it will get the null value in the F4 column and not in E4 column of the source sheet(Shipper VP), because the name of the persons are from B4 to D4, then there is an extra Grand Total column in E4. But in the range for rg,  I want only from B4 to D4 and not till the E4 column. The same is to be done for "n" persons in the Source sheet.</p>
  
  <p>If B4 is also a variable, then it is much better like ‘y’ or something. Then I can set ‘y’ as B4 as the starting cell number. And after that it will loop from B4 to the second last column value of Row 4 in the source sheet(ws.Source), since last column value of Row 4 will be Grand Total.</p>
  
  <p>I will be really grateful if somebody can help me out in this.</p>
  
  <p>Thanks.
  Regards, 
  Dip</p>