Insert Variable From Sheet Cell Into SQL Statement In VBA Code

Oct 29, 2013

I have the following code to execute a connection to a DB and return the result of the SQL statement. I have a variable in the statement that I want to be able to change from user input in a single cell. Unfortunately I am unable to get this to work.

Here is my code

cell_value = Sheets("some sheet").Range("P8")
Application.ScreenUpdating = False
Sheets("some sheet").Cells.Clear

Set rs = New ADODB.Recordset

[Code] ........

This fails and gives me a debug error, however if I declare the condition I want the code executes fine. This would mean having to update the code every time the condition changed or putting the whole query in a cell on the sheet which I do not want to do.

View 1 Replies


ADVERTISEMENT

IF Statement Within A COUNTIF Statement: Cell In Sheet "Summary" Count The Number Of Cells In Column DX Of Sheet "Analyses" That Are Greater Than 0

Apr 22, 2009

I am trying to have a cell in sheet "Summary" count the number of cells in column DX of sheet "Analyses" that are greater than 0, provided that the value in column A of "Analyses" corresponds with the value in B8 of sheet "Summary."

(In "Analyses," there are 106 subjects, each taking up 64 rows. So, columns 1-64 correspond to Subject 1, columns 65-128 correspond to subject 2, etc. In column DX, each subject has 64 values that are either 0 or greater than 0. In "Summary," each subject has one row that summarizes the 64 trials. I want a single cell in the "Summary," sheet to reflect the number of times each subject produces a value greater than 0 in column DX of "Analyses.") I tried using this formula, but it did not work correctly:

=COUNTIF(IF(Analyses!$A$1:$A$10000=Summary!B8,Analyses!$DX$1:$DX$10000,""),">0")

(Summary!B8 = 1, so I am trying to calculate the number of values in DX that are greater than 0 only for subject 1.) When I press enter, this yields a value of 384. This is impossible, given that subject 1 only has 64 possibilities of yielding a value greater than 0. Subject 1 has 2 values in column DX that are greater than 0. I tried making this an array formula by pressing Shift+Ctrl+Enter, and that just gives me a #VALUE! error.

View 5 Replies View Related

Insert Sheet & Name Macro Code

Apr 8, 2008

I am trying to insert 50 tabs (at different points in time during a macro) that each has a state abbreviation. I know how to get the macro to add a tab, but to name it, it wants to select " Sheet 4". The problem with this is, the tab that was created might be sheet 15 or sheet 1, and I need it to be able to name the tab no matter what sheet # it is.

View 7 Replies View Related

Referencing Sheet Code Names By Variable

Jul 18, 2006

I have a set of worksheets (Sheet31 through Sheet49 by codename) produced by a machine in the lab. I want to create a summary sheet that references cells in those worksheets.

Rather than go through the annoyance of doing this manually 40+ times I'm working on creating a macro to populate the summary sheet for me.

This is what I have so far

For J = 31 To 49
ResultSht = SheetJ.Name
Sheet1.Activate
ActiveSheet.Range("A4").End(xlDown).Offset(1, 0).Select

It hangs up on the "SheetJ.Name" however. My objective is to be using the object SheetJ, where the object SheetJ is the codename of the results sheet I'm trying to reference (for example, in the first pass through the loop it would be Sheet31). Could someone point out my mistake in this object reference?

View 9 Replies View Related

Add Formula Via Macro Code With Variable As Sheet Name

May 14, 2008

I have a front sheet with a list of all the sheets in the workbook in column A.
In column B I need to use the counta function to count the number of entries in the corresponding sheet to column A.

As there are 70 sheets I'd thought I'd use a Macro but I'm having great difficulties. I've done a search but can't find what I'm looking for.

So heres part of my code (it uses loops etc which I've managed but this just errors)

cell.Offset(0, 1).FormulaR1C1 = "=counta(" & str2&" C[-1])-1"

The str2 is the string name to reference the sheet in the formula (for example sheet1), I can't figure out how to enter this into the code so it works and is accepted.

View 3 Replies View Related

Code To Insert Rows In Next Sheet In Order Of Column B?

Apr 24, 2014

The below code used to work for me perfectly but in the last couple days it's been acting up. The code is supposed to cut out 2 rows from sheet1 and insert it in order of column B in sheet2. Up until a few days ago the code was inserting the 2, cut, rows properly but now it inserts it at the top of the sheet.

Here is the workbook I'm working with TEST.xlsm

View 1 Replies View Related

Insert A Variable Number Of Rows And Copy And Paste From And To Variable Positions

Aug 8, 2009

On the attached Excel file, I have code that will insert a variable number of rows and copy and paste from and to variable positions. That all works fine when run from a command button, but when I try to run it from the Worksheet_Calculate by entering 1 in J1 or K1 (inrange cell is J1+K1 for testing purposes) the CommandButton1_Click sub runs continously until an error occurs.

View 4 Replies View Related

Put Variable Code In Cell Using VBA

Jul 9, 2012

0
284.6554
419.3615
565.5539

[Code].....

The row on the top currently matches the column on the left side. This was done by using simple commands like "=A4", "=A5", "=A6" in the top row. Now I am trying to expand this table to a variable amount of columns and rows (there will always be 3 more rows than columns like there currently is above), so I want to write some code in VBA that will put these equations in the cells for me.

Here is the tricky part though. The numbers in the left column are actually calculated using the solver, and the numbers in the middle cells reference both the left column and top row. Because of this, I actually need to put an equation in the top row (like the previously mentioned "=A4") so that it always has the newest result from the solver.

The left column will always be in the same row, so only the number itself needs to change, but I'm not sure how to do this.

View 3 Replies View Related

Vba Code To Insert A Formula To Certain Cell

Aug 14, 2008

I try to put a formula to a certain cell using this code, but I get application defined or object defined error:

HTML Selection.Cells(1).Offset(0, 5).Formula = "=INDEX(range1;MATCH(""D""&C13;range2;0);MATCH(""S""&D13;range3;0))"

The weird thing is, that if I type the same formula itself to a cell, it works, so the problem shouldn't be with the formula:

HTML =INDEX(range1;MATCH("D"&C13;range2;0);MATCH("S"&D13;range3;0))

On the other hand I tried to replace the formula in my VBA code with simpler one, like this one and that worked aswell:

HTML Selection.Cells(1).Offset(0, 5).Formula = "=a1+a2"

View 10 Replies View Related

Code To Insert A Formula In Cell

Sep 2, 2009

I'm using the below to code to insert a formula in cell E1, what am i doing wrong here.

View 5 Replies View Related

IF Statement Referencing A Cell In Different Sheet

Oct 18, 2013

I am trying to reference a cell in a different sheet and am having a hard time getting the formula to return correctly. The IF statement is currently written like this,

IF(theotherworksheetcell="0",iftruethiscelltoequal"0",iffalseIwanttotypeasimpleformula)

Example:IF('BaseRent'J16="0","0",(D16*E16/12))

The current formula is returning $0 only, it should return a number in some cases.

View 2 Replies View Related

Insert Jpg Format Into Cell And Vlookup Them According To Code

Mar 29, 2014

In the sample i have list of codes,also i have picture according this codes: Can i somehow conected pictures and codes? My pictures are in jpg form,can i insert them into cell,where picture should has form as cell has.

When the code from the Data sheet in cell A2 sheet 1,than show the picture conected with this code in Data sheet.

View 2 Replies View Related

Insert Sheet- How Do I Write My Code To Add A Tab Labeled "Reports"

Mar 1, 2009

How do I write my code to add a tab labeled "Reports".

This is the code the Macro Recorder gave me, but it does not work.

Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "Reports"
Sheets("SetUp").Select

View 9 Replies View Related

Insert Relative R1C1 SUM Formula Into Cell Via Macro Code

Apr 24, 2008

Hello I'm looking for some clarification about how to use "= SUM(RC:RC)". I don't understand what the RC:RC represents. I thought that this was the directions for where to "position" the formula, but I think I'm missing something.

I was able to include the correct cells that I need summarized, but the answer is not right; it's zero.

Public Sub SubColumn(CritStr As String) ...

View 8 Replies View Related

Insert Sheet Name In A Cell

Jun 5, 2009

try to insert sheet name in a cell but without luck.

there is anyway to do this 'simple' operation?

View 9 Replies View Related

Find Method Code: Object Variable Or With Block Variable Not Set

Sep 8, 2006

I need my program to:
- find the cell containing the string "Datum/Tid"
- record the column and the row of the found cell in two variables lCol and lRow

Here is my

Sub test()

Dim rFoundCell As Range
Dim lRow As Long
Dim lCol As Long

'Find method of VBA
Set rFoundCell = Range("A1")
Set rFoundCell = Worksheets("Sheet1").Range("A1:Z50").Find(What:="Datum/Tid", After:=rFoundCell, _
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows, _
SearchDirection:=xlNext, MatchCase:=False)

'for anyof the two lines down I get the message "object variable OR block variable not set"

lRow = rFoundCell.Row
lCol = rFoundCell.Column

End Sub

View 5 Replies View Related

IF Statement - Assign Cell Value To Correct Sheet Tab For Current Month

May 17, 2014

Trying to assign this cell's value to the correct sheet tab for the current month.

Trying this:

Code:
=IF(MONTH(A3)=5,'[Wholesales 2014.xlsx]May'!$P$1,0),IF(MONTH(A3)=6,'[Wholesales 2014.xlsx]Jun'!$P$1,0)

A3 is todays date. Wholesales 2014.xlsx]May is a seprate sheet for the month of may and so forth for june.

The problem is the p1 value is empty for the Jun sheet so I get an error: #VALUE! (I assume that is why I am getting the error?)

Using:

Code:
=IF(MONTH(A3)=5,'[Wholesales 2014.xlsx]May'!$P$1,0)

By itself works just fine. I need to set this up for future use, though.

View 3 Replies View Related

Macro Code To Add Sum Formula/Function, With Variable Rows, To Cell

May 29, 2008

I am having to copy and paste rows of data into a new worksheet where the rows sizes change and I am wanting to add a new row at the end of the pasted rows but with the sumation formula to add the relevant column

e.g copy range B14:AA17 with in this case columns E to AA holding the numerical values. Therefore I wish in cell E18 to sum the value of E14:E17 and so on ending with cell AA18 holding the sum of AA14:AA17

As these vary I have all relevant variables, Range to add sumation values to eg E18:AA18
Start Cell E14 and so on.

I tried adding "=SUM(x:d)" where x and d are vars relating the the column cell required eg x = E14 and d = E17

View 3 Replies View Related

Add/Insert Sheet Named Off Cell

Oct 6, 2006

I am trying to get VBA to insert a new sheet and have it be renamed or just named by data that is in a certain cell. My attempt:

sheets.add.name = range("A1")

View 3 Replies View Related

VB Macro For Variable Cell Merge Into HTML Code Based On Criteria

Oct 7, 2009

Does anyone have a VB macro that will copy information from a cell (this will be variable based on criteria) and paste it into the middle of another cell that contains HTML code?

The criteria for the copy/insert/paste is that the information in the cell that is to be copy/inserted must match up with the correct number (part number).

I hope this makes sense. I have attached an example spreadsheet. (Pictures are worth 1000 words. ) Hope someone can help. I checked this site and couldn't find anything that was solved that matched the specifics.

View 6 Replies View Related

Insert Image With IF Statement

Jun 2, 2009

Is it possibe to insert an image (such as Picture1 which is the name of an image created by Insert>Name>Define) using an IF statement as in:

=IF(A1=B1,+Picture1,"")

View 2 Replies View Related

How To Insert More Conditions In If-statement

Feb 28, 2014

I am working on a search and replace macro. It's purpose is that within a region that varies only in row size, it's suppose to compare two values, A and B. A is only placed in one cell in the region, while B is a one column range in the region.

If A <> B, A should replace all B's in the region.

These regions are placed on top of each other in a long table. Please see the sample workbook, SampleDRW.xlsm

The code I am using is this:

[Code] ......

The problem is if the B value in the next region is the same as the one before but the A value change, my code don't pick up on it, because it only looks for changing B!

How can I expand the code to also pick up on this problem?

View 7 Replies View Related

Macro: Insert Sheet & Name As Cell Text

Dec 14, 2006

I have a workbook with single sheet called Fronpage. I want to insert a new sheet and rename the sheet based on the character value user specified in R1C1 in the Frontpage, and also would like the new sheet to be the last sheet in the workbook. I have tried to use DDE, and also Macro recording to do it. It didnt work out. Can anyone have any code to do it ?

View 2 Replies View Related

Insert Row On Sheet & Move Active Cell Row To It

Oct 23, 2007

I would like to create a macro that could archive entries from one sheet and insert them in another. I created one but the problem is that the entry has to be the same row each time.

Example:

Sheet 1 is current jobs and sheet 2 is old jobs.

My macro moves an entry from Row A-5 of Sheet 1 and moves it to the top of Sheet 2.

I would like to be able to scroll through each entry select it and have it moved to the top of the Old Jobs sheet.

View 9 Replies View Related

Use A Cell/variable As A Sheet Reference?

Jan 10, 2010

I have a formula that pulls data from another sheet using standard reference of sheetname! I want to build this forumla to use several sheets, and would like to pull the sheet names from other cells.

For example... instead of LEMONS!a1 and LIMES!B2

I would like to use REF1!a1 and REF2!B2

where REF1 and REF2 are pulled from cell Z1 which contains test 'Lemons' and cell Z2 which contains text 'Limes'

How can I do this?

View 9 Replies View Related

String Sql Insert Statement Syntax

Jul 16, 2009

In excel macro, I am trying to input data into sql server from excel ADO
what is the correct syntax for an insert statement using string sql? This table has two columns supplier ID autonumber and desc as varchar.

View 2 Replies View Related

Declaring Variable In Sub Statement?

Nov 23, 2011

I am trying to make sense of quite an elaborate macro built by someone else. This has the following line of code

Code:

Public Sub readOperation(rowNumber As Integer)
Dim rowOff As Integer
rowOff = rowNumber - 1
'other arguments and conditions
End Sub

What I am buggered about is that I cannot see the the variable rowNumber being declared anywhere but it seems to have a value of 1. I should mention that Operation is a class and I am very new to classes.

View 8 Replies View Related

How To Use Variable In R1C1 Statement

May 29, 2013

I need to know how to use a variable in an R1C1 statement. The statement is as follows:

Set rng = Sheet1.Range("A2:QFinalRowII").CurrentRegion

Ideally the statement would read something like,

Set rng = Sheet1.Range("A2:Q10").CurrentRegion

How would I use a variable name like "FinalRowII" instead of a number?

View 8 Replies View Related

Object Name As A Variable In A With Statement

Jan 16, 2007

Does the object in a 'With' have to be an object or can it be a variable containing the name of an object.

I want to carry out the same series of settings on a number of objects
and propose to define a Sub and pass the name of the objects as a parameter.
I then want to use a 'With' to set that object.
i.e. as shown below - though I cant see how it would work.

' combobox objects called Staff, Maker, etc

Call Setuplist("Staff")
Call Setuplist("Maker")
...

Private Sub Setuplist(whatlist As String)
'some complex filtering and sorting goes here
...
Set rnData = ActiveSheet.Range(whatlist+"List")
vaData = rnData.Value
With Me+whatlist ' this is the bit I cant fathom
.
.
End With
End Sub

View 9 Replies View Related

Insert New Row With String Based On Conditional Statement

Sep 6, 2008

I've been working on this project to propagate certain products through multiple categories. I chose to use excel to assign multiple categories to each product. On sheet1 I have setup products (column a) and qualifiers in the following columns (color, model, etc). What I would like to achieve is for the user to select yes or no for each category column and and if yes then have the corresponding category breadcrumb string (from sheet2) inserted into one specific column in sheet3. As the user continues to select multiple "yes" from the category columns for that single product, those additional category breadcrumbs get inserted at the end of the list in sheet3.

View 9 Replies View Related







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