Solved

how copy personal function vba to get data who update data to diferent cells excel

Posted on 2012-03-13
11
283 Views
Last Modified: 2012-03-13
hi..

i have a excel , at wich i make a vba function. when i write function to a cell as:

=GetSupplierId()

it run fine, and give me a sql data value fine

but when i copy -paste it to other cell, excel don't give me right value

when i copy it function to more 2 cells, only first cell give me right value, others cells copy same value than first cell copied

problem is i have more 1000 rows, and i can't write function one by one cell, and when i copy it , dont run fine

thanks a lot

i hope someone could help me
MINUTA-2284820110902-importante.xls
0
Comment
Question by:tenriquez199
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 48

Expert Comment

by:Martin Liss
ID: 37716562
Use Copy|PasteSpecial|Formulas
0
 
LVL 1

Author Comment

by:tenriquez199
ID: 37716628
hi MartinLiss

thanks your comment

i did it, but result is same..... only first cell give me fine data

example

i'm on cell k11,  and copy it

then mark cells k12 thru K14, and paste only formula , but result is it :

K12 8332.00        (is fine)
K13 8332.00        (is wrong)
K14 8332.00       (is wrong)


any other idea?

thank a lot sincerly
0
 
LVL 1

Author Comment

by:tenriquez199
ID: 37716665
reviewing my vba code i think here is the problem, cause i think the function activecell always take a cell value whre is located , so show same value

any other idea ? i think we need to change function activecell or something that

Function GetSupplierId()
   
   
    Dim importe  As Double
    Dim IdDoc As String
    IdDoc = ActiveCell.Offset(, -7)
    Rem IdDoc = cell.Text
 
    Dim conn As New ADODB.Connection
    Dim rs As ADODB.Recordset
    conn.Open ("Provider=sqloledb;Data Source=PCAENRIQUEZ\SQLEXPRESS;Initial Catalog=JDE;Integrated Security=SSPI")
    Set rs = conn.Execute("select importe from Facturacion where idDocumento = '" & Replace(IdDoc, "'", "''") & "'")
    GetSupplierId = rs.Fields(0).Value
 
    rs.Close
    conn.Close
    Set conn = Nothing
    Set rs = Nothing
End Function
0
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
LVL 48

Expert Comment

by:Martin Liss
ID: 37716751
Something like this maybe? I don't have time to test it.

Function GetSupplierId()
   
   
    Dim importe  As Double
    Dim IdDoc As String
    'IdDoc = ActiveCell.Offset(, -7)

Dim r As Range
Dim i As Long
    Rem IdDoc = cell.Text
 
    Dim conn As New ADODB.Connection
    Dim rs As ADODB.Recordset
    conn.Open ("Provider=sqloledb;Data Source=PCAENRIQUEZ\SQLEXPRESS;Initial Catalog=JDE;Integrated Security=SSPI")


    Set rs = conn.Execute("select importe from Facturacion where idDocumento = '" & Replace(IdDoc, "'", "''") & "'")

For i = 1 to 1000 ' change this to the 1000 rows you want to change like 24 to 1023
    r = ("A" & i) ' change 'A' to the correct column
    'GetSupplierId = rs.Fields(0).Value
    r.Value = rs.Fields(0).Value
Next
 
    rs.Close
    conn.Close
    Set conn = Nothing
    Set rs = Nothing
End Function
0
 
LVL 10

Accepted Solution

by:
SANTABABY earned 500 total points
ID: 37716803
Please make the following changes:

In Module 1: replace the GetSupplierId function code with the following code.

Function GetSupplierId(Optional row As Long = 0, Optional col As Long = 0)
    If row = 0 Or col = 0 Then
        GetSupplierId = "?"
        Exit Function
    End If
   
    Dim importe  As Double
    Dim IdDoc As String
    'IdDoc = ActiveCell.Offset(, -7)
    IdDoc = Cells(row, col - 7)
    Rem IdDoc = cell.Text
 
   
    Dim conn As New ADODB.Connection
    Dim rs As ADODB.Recordset
    conn.Open ("Provider=sqloledb;Data Source=PCAENRIQUEZ\SQLEXPRESS;Initial Catalog=JDE;Integrated Security=SSPI")
    Set rs = conn.Execute("select importe from Facturacion where idDocumento = '" & Replace(IdDoc, "'", "''") & "'")
    GetSupplierId = rs.Fields(0).Value
 
    rs.Close
    conn.Close
    Set conn = Nothing
    Set rs = Nothing
End Function



Then in you spreadsheet:
Change all cells  that have the formula =GetSupplierID to the following:
=GetSupplierId(ROW(),COLUMN())

Please let me know if it works for you.

Thanks,
SB
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 37716918
Dear Santa‚Ķ:)  Anyhow in my code I was trying to avoid the asker's code from opening and closing the database 1000 times and yours could do the same by do the Opening and Closing  once outside of the Function.
0
 
LVL 1

Author Comment

by:tenriquez199
ID: 37717538
hi MartinLiss

i have an error when run , i adebug VBA
error-vba.JPG
0
 
LVL 1

Author Comment

by:tenriquez199
ID: 37717543
Martinliss this is the code

Function GetSupplierId()
   
   
    Dim importe  As Double
    Dim IdDoc As String
    'IdDoc = ActiveCell.Offset(, -7)

Dim r As Range
Dim i As Long
    Rem IdDoc = cell.Text
 
    Dim conn As New ADODB.Connection
    Dim rs As ADODB.Recordset
    conn.Open ("Provider=sqloledb;Data Source=PCAENRIQUEZ\SQLEXPRESS;Initial Catalog=JDE;Integrated Security=SSPI")


    Set rs = conn.Execute("select importe from Facturacion where idDocumento = '" & Replace(IdDoc, "'", "''") & "'")

For i = 1 To 1459
    r = ("D" & i)
    'GetSupplierId = rs.Fields(0).Value
    r.Value = rs.Fields(0).Value
Next
 
    rs.Close
    conn.Close
    Set conn = Nothing
    Set rs = Nothing
End Function


............


the error is : onject variable or block with don't stabished
0
 
LVL 48

Expert Comment

by:Martin Liss
ID: 37717602
Forget about r

Change

For i = 1 To 1459
    r = ("D" & i)
    'GetSupplierId = rs.Fields(0).Value
    r.Value = rs.Fields(0).Value
Next

to

For i = 1 To 1459
    'GetSupplierId = rs.Fields(0).Value
   Range("D" & i) = rs.Fields(0).Value
Next
0
 
LVL 1

Author Comment

by:tenriquez199
ID: 37717679
hi Martinliss

i did the chnages , don't run fine send error name

Function GetSupplierId()
    Dim importe  As Double
    Dim IdDoc As String
    Dim r As Range
    Dim i As Long
    Dim conn As New ADODB.Connection
    Dim rs As ADODB.Recordset
    conn.Open ("Provider=sqloledb;Data Source=PCAENRIQUEZ\SQLEXPRESS;Initial Catalog=JDE;Integrated Security=SSPI")
    Set rs = conn.Execute("select importe from Facturacion where idDocumento = '" & Replace(IdDoc, "'", "''") & "'")
    For i = 1 To 1459
        Range("K" & i) = rs.Fields(0).Value
    Next
    rs.Close
    conn.Close
    Set conn = Nothing
    Set rs = Nothing
End Function
0
 
LVL 10

Expert Comment

by:SANTABABY
ID: 37717829
Don't you need to run the query for each row?

Function GetSupplierId()
    Dim importe  As Double
    Dim IdDoc As String
    Dim r As Range
    Dim i As Long
    Dim conn As New ADODB.Connection
    Dim rs As ADODB.Recordset
    conn.Open ("Provider=sqloledb;Data Source=PCAENRIQUEZ\SQLEXPRESS;Initial Catalog=JDE;Integrated Security=SSPI")
    For i = 1 To 1459
        IdDoc = Range("D" & i).Value
        Set rs = conn.Execute("select importe from Facturacion where idDocumento = '" & Replace(IdDoc, "'", "''") & "'")
        Range("K" & i) = rs.Fields(0).Value
        rs.Close
    Next
    conn.Close
    Set conn = Nothing
    Set rs = Nothing
End Function
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question