retrieve data from database

[RawanF]

hello everyone ,


I have an assigment for work and Its basically a code to check every row in a database that match three cratiria which will be defined by the user
name, month and year ,, it has to work through a huge database and then retrive the entire row , I got error in the IF line

    Dim db As Worksheet
    Dim inp As Worksheet
    Dim i As Long
    Dim lr As Long
    Dim Lrow As Long
    Set db = Sheets("DB")
    Set inp = Sheets("Input")
    Set r = db.Range("B:N")
    lr = Sheets("DB").Cells(Rows.Count, "c").End(xlUp).Row + 1
    i = 2
    Sheets("DB").Activate
    For i = 2 To lr
        If inp.Cells("K11").Value = db.Cells("d" & i).Value And inp.Cells("L11").Value = db.Cells("I" & i).Value And inp.Cells("M11").Value = db.Cells("J" & i).Value Then
            db.Range("B" & i & " :N" & i).Copy Sheets("rtv").Range("a" & i).pastevalues
        End If
        i = i + 1
    Next
    Lrow = Sheets("rtv").Cells(Row.Count, "a").End(xlUp).Row + 1
    Sheets("rtv").Range("a2:" & Lrow).Copy inp.Cells("c15").pastevalues

please help
thank you in advance

 

[Jerry Sullivan]

Re: retrive data from database

Welcome to MrExcel,

This syntax is incorrect to reference a cell:

  .Cells("K11")

Correct options include:

  .Cells(11, "K")

  .Range("K11")

 

[RawanF]

Re: retrive data from database

Welcome to MrExcel,

This syntax is incorrect to reference a cell:

  .Cells("K11")

Correct options include:

  .Cells(11, "K")

  .Range("K11")

Thank you , but it still shows me an error when I want to paste it in destination

 

[dmt32]

Re: retrive data from database

Hi,
as already pointed out by Jerry, you have some syntax errors in your coding.

not tested but see if update to your code helps:

Dim db As Worksheet
    Dim inp As Worksheet
    Dim i As Long
    Dim lr As Long
    Dim Lrow As Long
    
    Set db = Sheets("DB")
    Set inp = Sheets("Input")
    Set r = db.Range("B:N")
    
    lr = Sheets("DB").Cells(Sheets("DB").Rows.Count, "c").End(xlUp).Row + 1
    i = 2
    Sheets("DB").Activate
    For i = 2 To lr
        If inp.Range("K11").Value = db.Cells(i, "d").Value And _
            inp.Range("L11").Value = db.Cells(i, "I").Value And _
            inp.Range("M11").Value = db.Cells(i, "J").Value Then
            db.Range("B" & i & ":N" & i).Copy
            Sheets("rtv").Range("a" & i).PasteSpecial xlPasteValues
        End If
    i = i + 1
'clear clipboard
    Application.CutCopyMode = False
    Next


    Lrow = Sheets("rtv").Cells(Sheets("rtv").Rows.Count, "A").End(xlUp).Row + 1
    Sheets("rtv").Range("a2:a" & Lrow).Copy
    inp.Range("c15").PasteSpecial xlPasteValues
    Application.CutCopyMode = False

Dave