Select Range Table

rodrigo_m_almeida

New Member
Joined
Jan 13, 2022
Messages
42
Office Version
  1. 2021
Platform
  1. Windows
Good Morning,

I need to inform that the range is the column of rows of each date in the tables...

Does anyone have any idea how I could do this ?

table.PNG


Code :

VBA Code:
Public Sub ABC123()

    Dim xDate, xLine, xType, xQnty, L As Integer, xRange As Range
  
    L = 2

        Do Until IsEmpty(Cells(L, 1))
      
            Worksheets(1).Activate
      
            xDate = Cells(L, 1)
            xLine = Cells(L, 2)
            xType = Cells(L, 3)
            xQnty = Cells(L, 4)
          
            Worksheets(2).Activate
          
            xRange = Rows(3).Find(xDate).EntireColumn
          
            Range(xRange).Find(xLine, , , xType).Offset(, 2) = xQuanty
          
            L = L + 1
          
        Loop

End Sub

xxx.PNG
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Using Option Explicit would have shown that your spelling of the variable xQnty was incorrect near the bottom of the loop.
The Range.Find method fourth parameter should be either xlWhole or xlPart and is persistant so it defaults to what ever it was the last time Find was used.

maybe this ? although I'm unsure if you need to be using xlwhole or xlpart for each of the .Find
VBA Code:
Option Explicit


Public Sub TryThis()

    Dim xDate, xLine, xType, xQnty, L As Integer
    Dim xRange As Range, fndRng As Range
    
    Worksheets(1).Activate
        
    L = 2
    
        Do Until IsEmpty(Cells(L, 1))
        
            xDate = Cells(L, 1)
            xLine = Cells(L, 2)
            xType = Cells(L, 3)
            xQnty = Cells(L, 4)
            
            Set xRange = Worksheets(2).Rows(3).Find(xDate, , , xlWhole).EntireColumn
            With xRange
                Set fndRng = .Cells.Find(xLine, , , xlPart)
                If Not fndRng Is Nothing Then fndRng.Offset(, 2) = xQnty
            End With    'xRange
            
            L = L + 1
        
        Loop
        
End Sub
 
Upvote 0
Solution

Forum statistics

Threads
1,215,133
Messages
6,123,235
Members
449,092
Latest member
SCleaveland

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top