Select range of cells, given the address of the first one on top

[Alex Piotto]

Hi! Here we go...

From a ComboBox value in sheet1 i am trying to select a range in sheet2 , copy it and paste it in sheet1 in the right place.

sheet1 is named VENDAS
sheet2 is named DBase CLIENTES

I wrote a script, but i am stuck where i need to select the range below the selected cell in row1 (range named TUTTICLIENTI in DBase CLIENTES) while deselecting the first cell, from row2 down to row274.

Private Sub ComboBox1_Change()

Application.ScreenUpdating = False

Dim miocliente As String

miocliente = Worksheets("VENDAS").OLEObjects("ComboBox1").Object.Value   'get client name from combobox

Dim foundRng As Range

ThisWorkbook.Sheets("DBase CLIENTES").Activate

Set foundRng = ThisWorkbook.Sheets("DBase CLIENTES").Range("TUTTICLIENTI").Find(miocliente)   'look in DBase CLIENTES, range TUTTICLIENTI for same string

If miocliente = "Seleccionar Cliente" Then

'do nothing

Else

If foundRng.Value = miocliente Then

ThisWorkbook.Sheets("DBase CLIENTES").Range(foundRng.Address).Select      'HERE THE CELL WITH THE same STRING IS SELECTED

End If

MsgBox foundRng.Address  'USEFUL TO VIEW THE ADDRESS

'NOW I NEED TO SELECT AND COPY THE RANGE BELOW THE SELECTED CELL IN ROW1 (FROM ROW2 down TO ROW274), IN THE foundRng.Address COLUMN) - HELP HERE!




'FINALLY I WILL PASTE THE COPIED RANGE INTO VENDAS - NO PROBLEM HERE HOPEFULLY...

'ThisWorkbook.Sheets("VENDAS").Activate
'ThisWorkbook.Sheets("VENDAS").Range("B5:C277").Select
'ThisWorkbook.Sheets("VENDAS").Range("B5:C277").PasteSpecial Paste:=xlPasteValues
'ThisWorkbook.Sheets("VENDAS").Range("B5:C277").Borders.LineStyle = xlContinuous
'ThisWorkbook.Sheets("VENDAS").Range("A1").Select

End If
    
Application.ScreenUpdating = True

End Sub

What to do? A bit lost here...

 

[Alex Piotto]

my own solution

ThisWorkbook.Sheets("DBase CLIENTES").Range(Selection, Selection.Offset(274, 0)).Copy

 

[Dossfm0q]

is ListfillRange='DBase CLIENTES'!A2:A278

and paste VENDAS A1 then A2 next A3 or move A1 below then paste ComboBox value into A1 to be new result up

 

[Dossfm0q]

Is it Like this example?

Private Sub ComboBox1_Change()

Dim Sht1 As Worksheet, Sht2 As Worksheet
Dim ComBx1 As Object
Dim miocliente As String
Dim FndRng As Range, FndRngs As Range
Dim TUTTICLIENTI As Range

Set Sht1 = ThisWorkbook.Worksheets("VENDAS")
Set Sht2 = ThisWorkbook.Worksheets("DBase CLIENTES")
Set TUTTICLIENTI = Sht2.Range("A2:A274")
Set ComBx1 = Worksheets("VENDAS").OLEObjects("ComboBox1").Object
ComBx1.List = Application.WorksheetFunction.Transpose(TUTTICLIENTI)
miocliente = ComBx1.Value
Set FndRngs = TUTTICLIENTI.Offset(-1, 0).Resize(TUTTICLIENTI.Cells.Count + 1, 1)
Set FndRng = FndRngs.Find(What:=miocliente, After:=FndRngs.Cells(1, 1), LookIn:=xlFormulas, LookAt:= _
            xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
            , SearchFormat:=False)
''''''''''''''''
    With Sht1
    LstRw = .Cells(.Rows.Count, "A").End(xlUp).Row
    .Range("A" & LstRw + 1).Value = FndRng.Value
    End With
    
End Sub