Working With Recordset In A Range Of Cells

Oct 14, 2006

how i can display different values using the Selection.FormulaArray, ok look at my


Dim mySheet As Worksheet

' On Error GoTo errors
Dim x As Integer
x = TextBox1.Text
Dim y As Integer
y = RefEdit2.value

Set mySheet = Excel.ActiveSheet
mySheet. Range(RefEdit1).Select

Selection.FormulaArray = "=multiplie_value(""" & x & """, """ & y & """)"
MsgBox Selection.FormulaArray

Now the function i call:


Public Function multiplie_value(valor As Integer, multiplo As Integer) As Integer
multiplie_value = Val(valor) * multiplo
End Function

thats an example. so the real question is i create a form, the user enter some values to search to a database, the user select an area(a range) where they want the data to be display, but i dont how to display that info that is coming from the database in the range that the user selected. The info displayed in the range should look like a tabular report, column headers, etc.

View 9 Replies


ADVERTISEMENT

2007 / Range(Cells(n,n)) Not Working

Sep 13, 2008

I've worked with Excel for many years and have just begun writing VBA in Excel 2007, so I am really stumped by this:

SR = 1
SC = 1
TR = 40
TC = 2

Worksheets("ChartData").Activate

' Works
ActiveSheet.Range("A1:B40").Select

' All of the following Fail w/1004 Error
ActiveSheet.Range(Cells(1, 1)).Select
ActiveSheet.Range(Cells(1, 1), Cells(40, 2)).Select
ActiveSheet.Range(Cells(SR, SC), Cells(TR, TC)).Select

At first I thought I had a bad install,
but this is happening on two manufacturer installed systems...
And I seem to be following the documentation for the use of Range with Cells.
Am I missing something?

View 9 Replies View Related

Cells Range Reference Not Working Properly

May 25, 2009

I often have problems with the cells range reference method. For some reason I get an error and can't tell why. Other times it works fine. Is this just an unstable method to use or is this completely wrong? I'm assuming I am using the wrong syntax. It is definetly the range reference causing the problem.

Dim cnt1 as integer, cnt2 as integer,cnt3 as integer

cnt1 = 2
cnt2 = 50
cnt3 = 2

dim myrange as range

set myrange = sheets("sheet1").range(cells(2,cnt1), cells(cnt2,cnt3))

I have no idea why it won't work. I'm basically searching a columner range of cells.

View 2 Replies View Related

How To Use Offset With Recordset

Feb 25, 2013

i have a created a connection with Access table named Test, the table has 3 fields, ID,Firstname and LastName. The values in field firstName is filled in a Combox which is in Sheet1 of excel sheet.

so far i have done with the coding which create a connection with access table and fille all the firstname from the table to the Combobox using a recordset.

the code for filling combobox value from records set is like

Do Until RecordSet.EOF
shp.ControlFormat.AddItem RecordSet.Fields("Name").Value
RecordSet.MoveNext
Loop

Now what I need is whenever I select a name in a combobox the Cell A1 in excel should show me the LastName for that name.

I am not very sure if we can use offset in this.

View 3 Replies View Related

Open The Recordset

Dec 29, 2008

i have a select statement, that pulls one row from access and i need to get the data and assign it to different fields in a userform. however, because it only pulls one row, its at .eof and closes, so any following statements cause errors because the recordset is closed.

qc.Open "SELECT * FROM [Equipment_Table] WHERE [Equipment_QC] =" & combobox12.text, cn, adOpenStatic
Equipment_Title.text = qc.fields.item("Equipment_Title")
Equip_File = qc.fields.item("Equipment_File")
Equip_ID = qc.fields.item("Equipment_ID")

View 9 Replies View Related

Delete All Entries In Recordset ADO

Jan 25, 2012

I got a recordset which I get from a database (I use ADO).

I want to delete every entry in that recordset from the database.

View 4 Replies View Related

VBA - Unable To Get Data For Recordset

Feb 28, 2014

I've Created Connectionstring for excel which is in database format and trying to get the data by comparing 2 sheets using he below code.. But not working..

Set ws = Sheet1
sSQL = ""
sSQL = "SELECT [Company Code], "

[Code].....

View 1 Replies View Related

PasteSpecial Format CSV From Recordset

Jul 28, 2008

Currently, my code involves making a connection to a db then run queries. Results from the queries goes to recordset & from the recordset copy to specified range in excel worksheet. I am trying to do an automation process.

The problem is that once the record is pasted in excel worksheet, the date column is not being recognised as date therefore excel function (vlookup) is not giving me the results in my report.

When i do a manual PasteSpecail as CSV into the worksheet from the query result, my report gets populated with data which is correct.

i've tried doing a pastespecial format:="CSV" but it doesnt work.

Is there any codes that i can use to copy from the recordset as a CSV format pastespecial??

Or any other ways that can be done?

View 9 Replies View Related

Insert Ado Recordset Into Table

Sep 23, 2009

I'm getting extremely frustrated with trying to find some code that allows me to paste an ADO recordset into a database table!

What I am doing is on a weekly basis is creating a plan of which containers my company needs to devan. We have a system that stores shipping information about these containers and I get shipping information delivered to me via e-mail from one of our major shipping companies. Their data is more accurate so what I do is paste the data I get from them into my container planning sheet and import it into out Teradata Warehouse to query against using ADO and I import this one record at a time. This is extremely slow!

In order to get around this I want to create a recordset from this data and then paste the recordset into a table in the database. So far this is my ....

View 9 Replies View Related

VBA: Create PivotTable From A DAO Recordset

Sep 24, 2009

I have been creating pivot tables (from the active workbook) with ADO for a while and its working great.

I just tried to switch to DAO and adapted my code accordingly but the new code fail at:

Code: ....

View 9 Replies View Related

Pivot Table From Vba Ado Recordset

Nov 15, 2006

Is there an example outthere that will demonstrate how to create a pivot table using an ADO Recordset? I'll be experimenting tommorow but really don't relish the thought of spending 12 hours trying to figure it out.

View 2 Replies View Related

Sporadic Headers When Reading CSV Into Recordset

Nov 8, 2011

When I read a CSV file into a recordset, the headers are not all being read. I get gaps in the headers for whatever reason. Attached below is the Function to load the recordset from the file. I have hard coded Hdr to "No" in the connection string.

Code:

Function QueryByID(tableName As String, fieldToQuery As String, Target As Long, Hdr As String) As Recordset
Dim strFilePath, strFileName, strQuery As String
Dim oFSObj, oConn As Object
Dim oRs As Recordset
Dim f, lastRow As Integer

[Code] .......

The below is how I output the resultset:

Code:

Sub OutputResultSet(aTable As String)
Dim row, col, i, j As Integer
row = 1
col = 1
ThisWorkbook.Sheets(aTable).Activate

[Code] .....

I have triple checked the CSV file and the first row in each file has definitely got headers. Some headers are loaded and output fine, others are just blank.

View 5 Replies View Related

Filling Listbox With Recordset From Access

Jan 17, 2012

I want to fill a listbox with values from a database.

The query et al. works just fine, but I cant get the listbox filled.

Is there a command to fill the listbox with all the entries in the recordset at once?

View 5 Replies View Related

Current Recordset Converts In An Array

Nov 30, 2009

I have a sub-routine I call on that takes a current recordset and converts it to an Array.

I would like to make this sub-routine tool more flexable by being able to specify THE NAMES for the recordset and an array involved.

For example

Sub My_Code()

....blal bla bla.... current code...

Call Recordset_To_Array( Recordset_name, Array_name )

....bla bla bla... continued

end sub
And the subroutine tool:

Sub Recordset_To_Array( Recordset_name as String, Array_name as string )

'Code that generates RS
'Need to take Recordset_name string value and use it in the line below at "XXXXX"
Dim XXXXX as Recordset

'Code that generates array
'Need to take Array_name string value and use it in the line below at "XXXXX"
Dim XXXXX as Variant
End Sub

View 9 Replies View Related

VBA Recordset Not Returning Updated Results From Query?

May 9, 2012

I have a function that loops through sheets and opens a query and copys the information to a sheet. I have gone in the access db and updated the query. Yet everytime i call it in excel it returns the old data. I have gone into access and verified what data I should get and I just don't get it.

I pass in the query name and the sheet name into qryName and sheetName name.

Code:
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sQRY As String
Dim strFilePath As String

[Code].....

View 1 Replies View Related

Return PreStored Query Result Into Recordset Through Excel

Sep 12, 2013

I am trying to run a prestored query in a Access database and popuate the result into a Recordset --- through Excel. So far, I have managed to accomplish that using the code below. However, now I need to update some records in the recordset. The code below does not allow me to do so.

The following message prompted when the code tried to update the record: Run-time error '3251' Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

This is written for other users who only knows how to create a query in Access. And the users prefer not to import the query results to spreadsheet.

*Someone suggested use Recordset.Open however, that seems to work only with SQL statement but not prestored Queries.

Code:
Sub RunExistingQuery()
'Execute prestored queries in Access
Dim Con As ADODB.Connection

[Code].....

View 2 Replies View Related

Create Pivot Table Based On Access Recordset

Nov 1, 2008

I'm looking for an example of creating a pivot table based on an ADO Access Recordset.

I understand I can use MS Query - which I am, but looking to see if an alternative exists. A table I am inetrested in currently has 400,000 records I would like to retrieve and pivot.

View 9 Replies View Related

Problem Displaying Single Record In Userform Listbox From Recordset

Jul 20, 2009

I am trying to populate a 3 column listbox in a userform from SQL Server via ADO. When the result set consists of more than one record, there is no problem and the data is displayed properly (ie each piece of data is in its appropriate column) eg...

StockCode..........QtyReqd.........JobDeliveryDate
test1...................1................. 01/01/1900
test2...................1..................31/12/1900
test3...................3..................18/02/1900

however when the recordset returns only a single record, the data does not transpose and views as below (ie each piece of data in the record is on a different line in the first column

StockCode...........QtyReqd.........JobDeliveryDate
test1
1
01/01/1900

View 12 Replies View Related

Excel 2010 :: ADO Recordset Query Quandary Locating Duplicates

Mar 13, 2013

I am having trouble setting up a query for a recordset that will list duplicates for an automated process to clean up. I am using Excel 2010 with Microsoft ActiveX Data Objects Recordset 6.0 Library and Microsoft ActiveX Data Objects 6.1 Library as references.

So far, I have chopped the query down to it's most basic elements and reassembled until it broke. Necessary pieces work well, but when I combine them, I get nowhere.

Here is the working simple query to retrieve a single row:

Code:
dupeSQL = "SELECT PSID FROM [Obstructed$] "

Here is the working single query to retrieve a count:

Code:
dupeSQL = "SELECT COUNT(PSID) As '" & "CountPSID" & "' " _
& "FROM [Obstructed$] "

When combined, I cannot figure out why it won't work:

Code:
dupeSQL = "SELECT [PSID], COUNT(PSID) As '" & "CountPSID" & "' " _
& "FROM [Obstructed$] "

Here is what I am trying to get to:

Code:
dupeSQL = "SELECT [PSID], COUNT(PSID) As '" & "CountPSID" & "' " _
& "FROM [Obstructed$] " _
& "WHERE COUNT(PSID) > 1 " _
& "GROUP BY PSID"

I can even work without the GROUP BY. I have also played with the HAVING clause to try and accommodate the [PSID] field, but have gotten nowhere.

View 1 Replies View Related

Cells Not Being Populated With Exact Same Formula As Previous Working Cells

Jun 27, 2008

formula using if's and vlookup's to populate a calendar with x's for day a person is gone. however the last couple of people that are gone are not being marked on the calendar even though i use the exact same formula (except for cell numbers and such) for previous, working entries. Here is my formula

View 9 Replies View Related

Find Function Not Working On Cells Which Reference Other Cells?

Oct 31, 2011

I am using the find function to establish the row number in a spreadsheet for an edit process.

Code:
wks.Range("B:B").find("something").Row

the cells in Range("B:B") are linked to another worksheet. If I copy paste values on the cells in Range("B:B") the code above works a treat.

View 4 Replies View Related

Range Is Not Working

Apr 13, 2009

i dont know what's wrong with these lines:

View 3 Replies View Related

Range Sort Not Working?

Jun 27, 2014

I'm highlighting a range and then sorting column F from Largest to Smallest. The macro doesn't sort it at all. Added my code tags.

View 1 Replies View Related

VBA Sorting Nth Range Not Working

May 16, 2009

I have a values in column C of stuff1, stuff2 and on down.

I have a value in cell A1.

I want to start in C1, and every Nth, where N is the value in cell A1, select and paste into column H.

My problem is that the selected cells don't start in C1, in fact, based on the value in A1, it starts in different cells in column C. AND, if i press the button twice, the returned values are different.

View 14 Replies View Related

Range Select Not Working?

Feb 17, 2012

The code works up the If statement and I'm not sure why.

Code:
Private Sub SalesRedeemed_Click()
' Open SalesRedeemed Worksheet move to next month
Sheets("SalesRed").Select
Sheets("SalesRed").Range("B5").Select
If Range("B5") > 1 Then
Sheets("SalesRed").Range("B6").Select
End If
End Sub

View 9 Replies View Related

Working With A Variable Range

Feb 14, 2009

My MACRO is used daily to copy data from several sheets resulting in a new sheet with a differing number of rows with every run. My problem is that I need to define this range of rows in order to do an AUTOFILL function.

I know I can use a cell to hold the row count using COUNTA. However, I cannot figure out how to then use the result of this in the range statement.
example using COUNTA within cell D100 produces a row count of 95

My MACRO needs to do the following:
A1 =Month(C1)
B1 =Year(C1)
Range("A1:B1").Select
Selection.AutoFill Destination:=Range("A2:B????"),

View 9 Replies View Related

Indirect Using Dynamic Range Not Working

Dec 27, 2012

In the example I have attached there are two sheets, Details and DataTables. There are also a number of named ranges, both static and dynamic, that refer to data on the DataTables sheet. Most of the static ranges were added for debugging purposes. The tables have been shortened for this example.

On the Details sheet column A has a drop down that allows you to select the make of a device. Column B builds its drop down list based on what is selected in column A. The data validation in B2 uses a named dynamic range and the drop down does not work. The data validation in C2 used a named static range and the drop down works.

Column C will fill in the RU value based on what is selected in column B. Cell C3 uses named dynamic range and it does not work. Cell C4 uses named static range and it works.

I would like to used named dynamic ranges if possible so when data is added to the tables the named dynamic ranges will self adjust. Can what I want to do be done?

Dynamic Range.xlsx

View 8 Replies View Related

Date Range Formula Not Working

Apr 14, 2014

I am trying to create a formula that if the date column falls between 01/01/2014-01/25/2014, it returns January as the value.

View 10 Replies View Related

Date Range Formula Not Working?

Apr 14, 2014

see the attached spreadsheet, I am getting an error message with the attached formula

View 2 Replies View Related

Named Range Not Working In Some Functions

Apr 29, 2012

I have a problem using a named range as an arg to worksheet functions.

First, I create 2 end point names for the range, myStart, myEnd.
Then I create a named range myRange = myStart:myEnd

This formula in a cell works: SUM(myRange)
This formula doesn't: COUNT(myRange) . It crashes Excel, requests Restart.
But these work: COUNT(myStart:myEnd) and SUM(myStart:myEnd)

Why does SUM work for either set of args, but COUNT only works for one set?

Here is the F3, PasteList output:

mFirstCol =mFirstColMinus1+1
mFirstColMinus1 =COLUMN(Sheet1!$A:$A)
mInsertCol =COLUMN(Sheet1!$B:$B)
mLastCol =MAX(mFirstCol, mInsertCol-1)
mNumberCols =mInsertCol-mFirstCol
mRowEnd =INDIRECT(ADDRESS(1,mLastCol))
mRowRange =mRowStart:mRowEnd
mRowStart =INDIRECT(ADDRESS(1,mFirstCol))

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved