retrieve data from database

RawanF

New Member
Joined
May 2, 2017
Messages
2
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


Code:
    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
 
Last edited by a moderator:

Jerry Sullivan

MrExcel MVP
Joined
Mar 18, 2010
Messages
8,787
Re: retrive data from database

Welcome to MrExcel,

This syntax is incorrect to reference a cell:
Code:
  .Cells("K11")
Correct options include:
Code:
  .Cells(11, "K")
Code:
  .Range("K11")
 

RawanF

New Member
Joined
May 2, 2017
Messages
2
Re: retrive data from database

Welcome to MrExcel,

This syntax is incorrect to reference a cell:
Code:
  .Cells("K11")
Correct options include:
Code:
  .Cells(11, "K")
Code:
  .Range("K11")
Thank you , but it still shows me an error when I want to paste it in destination :(
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,251
Office Version
2013
Platform
Windows
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:


Code:
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
 

Forum statistics

Threads
1,081,693
Messages
5,360,668
Members
400,592
Latest member
katekoz

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top