tenriquez199
asked on
how copy personal function vba to get data who update data to diferent cells excel
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
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
Martin Liss
Use Copy|PasteSpecial|Formulas
ASKER
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
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
ASKER
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\SQLEXPR
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
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\SQLEXPR
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
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\SQLEXPR
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
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\SQLEXPR
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
ASKER
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\SQLEXPR
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
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\SQLEXPR
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
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
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
ASKER
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\SQLEXPR
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
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\SQLEXPR
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
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\SQLEXPR
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
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\SQLEXPR
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