DaniWeb Forum Index > Software Development > Visual Basic 4 / 5 / 6

Visual Basic 4 / 5 / 6 RSS

Macro for "Sumif/Vlookup"

•

Join Date: Mar 2009

Posts: 12

Reputation: shahji is an unknown quantity at this point

Solved Threads: 0

shahji

Offline

Newbie Poster

Macro for "Sumif/Vlookup"

May 5th, 2009

Hello,

i am working on a macro where i need to copy data from a sheet to a different sheet in the same workbook. i normally use "vlookup" or "Sumif" or "Sumproduct" formulae to do the job. the datas is quite large and it takes ages to refresh the data when i use "Sumif" or "Vlookup". i am looking for a macro which could replace these formulae. I have included a short example of the database.

Sheet1 has the data and it should be pasted on Sheet2 by looking in Sheet1 column "Code" should be used as a condition. for every "700" there are a few value and they should be taken from Sheet1 and pasted in Sheet2 corresponding to the "Client". same with other "Codes" and i might need to add the Balances in sum instances.

thank you in advance for your help,

Shah

•

Join Date: Apr 2007

Posts: 1,094

Reputation: cguan_77 has a little shameless behaviour in the past

Solved Threads: 91

cguan_77

Offline

Veteran Poster

Re: Macro for "Sumif/Vlookup"

May 10th, 2009

you're looking for a specific value in sheet1 to pasted in sheet2?
or you need to do calculation first in sheet1 and after doing the calculation you'll find for a specific result and copy over to sheet2?
is it what you're trying to do or what?

•

Join Date: Mar 2009

Posts: 12

Reputation: shahji is an unknown quantity at this point

Solved Threads: 0

shahji

Offline

Newbie Poster

Re: Macro for "Sumif/Vlookup"

May 11th, 2009

well thank you for your reply,

I would not have to calculate anything as all the values are already in the database. i would need to find a specific result and copy over to sheet2. i hope this helps,

thanks,

Shah

•

Join Date: Apr 2007

Posts: 1,094

Reputation: cguan_77 has a little shameless behaviour in the past

Solved Threads: 91

cguan_77

Offline

Veteran Poster

Re: Macro for "Sumif/Vlookup"

May 12th, 2009

try this..
it will find a specific value..then copy the whole rows to sheet2.. just modify it to suit your needs.. hope it helps..

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
Dim x as boolean
Dim x1 as string
 
x = Cells.Find(What:="Data to Find", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
 
if x = true then
 
x1 =activecell.row
 
rows(x1).copy
 
sheet2.range("1:1").pastespecial
 
else 
 
msgbox "Data not found"
 
end if
Dim x as boolean
Dim x1 as string

x = Cells.Find(What:="Data to Find", After:=ActiveCell, LookIn:=xlValues, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate

if x = true then

x1 =activecell.row

rows(x1).copy

sheet2.range("1:1").pastespecial

else 

msgbox "Data not found"

end if

Last edited by cguan_77; May 12th, 2009 at 12:58 am.

•

Join Date: Mar 2009

Posts: 12

Reputation: shahji is an unknown quantity at this point

Solved Threads: 0

shahji

Offline

Newbie Poster

Re: Macro for "Sumif/Vlookup"

May 13th, 2009

thank you cguan,

the code works fine.
i was wondering if i could run a loop on this as the code stops after looking at the first value in sheet1 and copies it to sheet2.

Also is it possible to give it more than one codition lets say it looks in sheet2 column1 for a value and copy a value from sheet1 column 3 corresponding to the value in sheet2 column1 and a condition in sheet1 column2.

i hope i have explained it ok.

thank you

Last edited by shahji; May 13th, 2009 at 10:08 am.

•

Join Date: Apr 2007

Posts: 1,094

Reputation: cguan_77 has a little shameless behaviour in the past

Solved Threads: 91

cguan_77

Offline

Veteran Poster

Re: Macro for "Sumif/Vlookup"

May 14th, 2009

yes it is possible to look for a value in sheet column1 and copy a value from sheet1 column3 but what you mean by "and a condition in sheet1 column2"?

•

Join Date: Mar 2009

Posts: 12

Reputation: shahji is an unknown quantity at this point

Solved Threads: 0

shahji

Offline

Newbie Poster

Re: Macro for "Sumif/Vlookup"

May 14th, 2009

thank for getting back to me,

well i have attached a small example.

sheet1 has three columns "Client", "Code" and "Balance" and sheet2 has two columns "Client"and "Balance".

i want to copy data from Sheet1 "Balance" to Sheet2 "Balance". the data should correspond to the "client" column.
there are many entries in "Code" but i am just interested in a few. lets say "Code" is "700" and i want to copy "balance" for Client "0014" and past it in sheet2 "Balance" corresponding to the client.

i hope this will help,

btw i was able to loop the macro but as it is not dynamic it just keeps on going through the sheets and copies nothing.

thank you for your help.

Attached Files

example.zip (21.6 KB, 8 views)

•

Join Date: Apr 2007

Posts: 1,094

Reputation: cguan_77 has a little shameless behaviour in the past

Solved Threads: 91

cguan_77

Offline

Veteran Poster

Re: Macro for "Sumif/Vlookup"

May 15th, 2009

hi shahji, try this, hope it helps...

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
Sub Macro1()
 
Dim x, y As Boolean
Dim x1, x2, x3 As String
Dim y1, y2, y3 As String
 
'declare the data to find
y1 = "700"
y2 = "0014"
 
    'activate sheet1 and search
    Sheets("Sheet1").Select
    Columns("b:b").Select
    x = Selection.Find(What:=y1, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
 
    'check if data is found
    If x = True Then
 
     x1 = ActiveCell.Row
     x2 = Cells(x1, 1).Value
     x3 = ActiveCell.Value
 
    End If
 
    'check if data match together if not search again
    If x2 <> y2 Then
 
        y = Cells.FindNext(After:=ActiveCell).Activate
 
          Else
 
    End If
 
  'if data found found then copy
  If y = True Then
        y3 = ActiveCell.Row
        Rows(y3).Select
        Rows(y3).Copy
  End If
 
   'select sheet2 and search the data
   Sheets("Sheet2").Select
   Columns("a:a").Select
   y = Selection.Find(What:=y2, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
 
  ' found then paste the data from sheet1
  If y = True Then
   x1 = ActiveCell.Row
   Cells(x1, 1).Select
   Selection.PasteSpecial
 
End If
 
End Sub
Sub Macro1()

Dim x, y As Boolean
Dim x1, x2, x3 As String
Dim y1, y2, y3 As String
    
'declare the data to find
y1 = "700"
y2 = "0014"

    'activate sheet1 and search
    Sheets("Sheet1").Select
    Columns("b:b").Select
    x = Selection.Find(What:=y1, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        
    'check if data is found
    If x = True Then
    
     x1 = ActiveCell.Row
     x2 = Cells(x1, 1).Value
     x3 = ActiveCell.Value
    
    End If
    
    'check if data match together if not search again
    If x2 <> y2 Then
    
        y = Cells.FindNext(After:=ActiveCell).Activate
    
          Else
     
    End If
 
  'if data found found then copy
  If y = True Then
        y3 = ActiveCell.Row
        Rows(y3).Select
        Rows(y3).Copy
  End If
    
   'select sheet2 and search the data
   Sheets("Sheet2").Select
   Columns("a:a").Select
   y = Selection.Find(What:=y2, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

  ' found then paste the data from sheet1
  If y = True Then
   x1 = ActiveCell.Row
   Cells(x1, 1).Select
   Selection.PasteSpecial
  
End If

End Sub

•

Join Date: Mar 2009

Posts: 12

Reputation: shahji is an unknown quantity at this point

Solved Threads: 0

shahji

Offline

Newbie Poster

Re: Macro for "Sumif/Vlookup"

May 15th, 2009

hey Cguan,

i have tried the above but it copies the macro to sheet2 rather than any data.
any ideas why is that?

thanks

•

Join Date: Apr 2007

Posts: 1,094

Reputation: cguan_77 has a little shameless behaviour in the past

Solved Threads: 91

cguan_77

Offline

Veteran Poster

Re: Macro for "Sumif/Vlookup"

#10

May 15th, 2009

try this..

 Help with Code Tags 
 Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text) 
Sub Macro1()
 
Dim x, y As Boolean
Dim x1, x2, x3 As String
Dim y1, y2 As String
 
'declare the data to find
y1 = "700"
y2 = "0014"
 
    'activate sheet1 and search
    Sheets("Sheet1").Select
    Columns("b:b").Select
    x = Selection.Find(What:=y1, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
 
    'check if data found then copy
    If x = True Then
 
     x1 = ActiveCell.Row
     x3 = ActiveCell.Value
     Rows(x1).Select
     Selection.Copy
 
    End If
 
 
   'select sheet2 and search the data
   Sheets("Sheet2").Select
   Columns("a:a").Select
   y = Selection.Find(What:=y2, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
 
   'found then paste the data from sheet1
  If y = True Then
   x1 = ActiveCell.Row
   Cells(x1, 1).Select
   Selection.PasteSpecial
 
End If
 
End Sub
Sub Macro1()

Dim x, y As Boolean
Dim x1, x2, x3 As String
Dim y1, y2 As String
    
'declare the data to find
y1 = "700"
y2 = "0014"

    'activate sheet1 and search
    Sheets("Sheet1").Select
    Columns("b:b").Select
    x = Selection.Find(What:=y1, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
        
    'check if data found then copy
    If x = True Then
    
     x1 = ActiveCell.Row
     x3 = ActiveCell.Value
     Rows(x1).Select
     Selection.Copy
     
    End If
      
      
   'select sheet2 and search the data
   Sheets("Sheet2").Select
   Columns("a:a").Select
   y = Selection.Find(What:=y2, After:=ActiveCell, LookIn:=xlFormulas, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate

   'found then paste the data from sheet1
  If y = True Then
   x1 = ActiveCell.Row
   Cells(x1, 1).Select
   Selection.PasteSpecial
  
End If

End Sub