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


ADVERTISEMENT

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

VBA With If Statement Syntax

Jan 21, 2009

I have the following code that loops thru and puts the word 'TEST' in column J if column B has a TEXT value of '020'

----------------------------------------------------------------------------------
With Range("J1:J" & Lastrow)
.Clear
Range(.Cells(2), .Cells(.Count)).Formula = "=if((b2)=""020"",""TEST"","""")"
End With
----------------------------------------------------------------------------------

Questions
1) why do I need double quotes "" "" on every argument in the function? Is it because I'm working with TEXT data?
2)I want to use multiple ifs (ie if cell b2 equals '020' OR '030' then put the word 'TEST' in col J). How to change the syntax to do this?

View 9 Replies View Related

If Statement (check Syntax)

Jan 24, 2010

I want to check something syntax wise about IF Statement
My line of code is as follows

View 2 Replies View Related

Writing To File May Require Special Print Statement Syntax

Jan 22, 2013

Code:
' write data to txt file
For j = 1 To 3
For k = 1 To 21
Print FNum, OPTOarray(j, k)
Next k
Next j

I am writing an Array to a text file but I keep getting an error that the Print statement requires a suitable object. I tried Debug but all this did was put the data into the immediate window and created a file that was empty.

"????.Print FNum, OPTOarray(j, k)".

The data in the immediate window is correct but when you open the text file with notepad there is nothing to be seen.

View 1 Replies View Related

Insert Row Syntax - Resize Row VBA Error

Sep 15, 2009

I need to insert rows in row A44 depending how many cells exits in a range i named "ALL_C"

I saw this code that inserts 10 rows on A44 so i thought by modifying it the way i did would work but it didn't. I'm still new at this stuff. What am i doing wrong?

Original

Sub Insertinrow43()
Range("A44").Resize(10, 1).EntireRow.Insert
End Sub
Modified (doesn't work)

Sub Insertinrow43()
Range("A44").Resize(Count(All_C), 1).EntireRow.Insert
End Sub
Thank you!

View 9 Replies View Related

VBA To Search String And Insert Row If String Not Found

Apr 11, 2013

I have a spreadsheet which has "Employee: [agent 1 name]" in column A and it may or may not have the word "Break" in the same column before it mentions "Employee: [agent 2 name]". The amount of data between agent 1 and agent 2 varies and am needing code which will insert a row above "Employee: [agent 2 name]" if "Break" is not found, and add the word "Break" in column A on the inserted row. I would need this to loop through the spreadsheet until all 100+ agents have been searched.

I'm also needing this done for the word "Meeting" and would insert a row 2 rows above the next agent.

View 6 Replies View Related

Insert String And String Variable Into Cell

Feb 18, 2014

I'm using a userform to create a new sheet. The form already creates the sheet and names it what was typed into the userform. Now I want it to place that variable in a cell along with a string. the following code will place the variable from the form (tbname) into cell b5.

View 2 Replies View Related

If Else Syntax: Change Some Outputs Of The Macro Without Changing The Syntax

Jul 19, 2006

Im writting a macro. It works find until a certain point. When I want to change some outputs of the macro without changing the syntax, it display an error mesage while runing the macro. It says Else without If. Which is quite disturbing because the Else was not creating any problem before. Here is my macro before I changed the conditions (this one work nicely)

Sub Copy_Sheet_Beta()
Set wba = ActiveWorkbook
On Error Resume Next
If IsWorkbookOpened("Projekt.xls", "C:Documents and SettingsfrederikSkrivebordRedd Barna") Then
Workbooks("Projekt.xls").Activate 'In case open, just activate "Projekt"
Else
Workbooks.Open Filename:="C:Documents and SettingsfrederikSkrivebordRedd Barnaprojekt.xls"
End If
Set wb = Workbooks("Projekt.xls")
wb.Activate
If Not SheetExists(wba.ActiveSheet. Range("C1").Value) Then
MsgBox "overall doesn't exist!"
Else........................................

View 2 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

If Statement That Return A String Of Text?

Jan 9, 2009

not sure if an "IF" staement is appropriate?

I have 3 columns containing data:

column A has text in it,
example: A2="red", A3="green", A4="blue"

column B has 3 letter month in it and they could have repeats
example: B2="Jan", B3="Feb", B4="Jan"

C2="Jan"

in D2 list anything column A that matches C2
example: D2="red, blue"

View 11 Replies View Related

Condition Statement Based On 2nd To Last Character Of String?

Apr 10, 2012

I am trying to pull certain members loads from our global design force spreadsheet and because of the naming convention we used for our structural members the 2nd to last character is unique to what I am looking for. I am trying to get a simple condition statement that will display the load only if the 2nd to last character of the name of the element is satisfied.

For Example, if "T" is what I am looking for then :

EDT4 will be true and give me the value accordingly
D10T1 Will also be true and return me the value
D10B2 Will not be true

I know how to use the Mid() and right() function, but I need to check the 2nd from the right.

View 2 Replies View Related

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 View Related

Insert A Zero On The Left Of A String?

Jan 22, 2014

this formula as a result =IF(LEFT(A3,1)="0",RIGHT(A3,LEN(A3)-1),A3) It worked great to strip the left most zero from a string of text numbers in the cells where the formula was applied.

However, now I need to put the zero back in lol!

So...how would I adjust the formula (or write a different one) to insert a zero as the first digit of a string of text digits?

View 4 Replies View Related

String If Text Insert Date

Feb 11, 2010

I am trying to setup a variable from a dropdown selection box. Basically, If "Completed" is selected in the box, current date is set in the corresponding field.

The formula I have tried is:
=IF(F5="Completed",G5="",G5=NOW())

I have a variable string setup opposite of what I am going for that works:
=IF(AND(D5<>"Assigned",D5<>""),IF(E5="",NOW(),E5),"")

So if anything is showing other than assigned the date is input. But have not been able to reverse this for the desired output.

View 2 Replies View Related

Automatically Insert Space In A String

Dec 18, 2008

I have a column called "Unit Size," which contains data such as 200 g, 0.32 g, 15 mL, 2 mg.

I need a macro to automatically insert a space between the numbers and the letters, for example, if someone types 10g it automatically converts it to 10 g.

The strings may be of different lengths, i.e. contain different numbers of decimal places or signficant figures.

View 4 Replies View Related

Insert Double-comma At End Of String

May 25, 2008

i'm a complete excel novice, but i think this should be pretty easy for you guys...

i just bought a new satnav system after 4 years with tomtom...

anyway, to get the speed camera database to work with it, i must add a couple of commas at the end of each set of co-ordinates contained within the spreadsheet, like this:

-3.23956,51.52737,1,50,1,67,,
-5.29024,50.22664,1,50,1,67,,
-5.27461,50.23143,1,50,1,247,,

thing is, most of them don't have the 2 commas at the end and just end with the number, like this:

-3.23956,51.52737,1,50,1,67
-5.29024,50.22664,1,50,1,67,,
-5.27461,50.23143,1,50,1,247

therefore, what i need to do is make up a formula in excel to search for a double comma at the end of each set of co-ordinates and if it doesn't already have them, then it should insert them...

i'm guessing for anyone with excel experience, this should be pretty easy to solve...

View 9 Replies View Related

Insert Row Macro Based On Text String

Nov 20, 2009

I'd like to adapt my macro so that it would insert a blank row after it detects the the first 16 characters of text as "'Closing Balance"; or it could even detect "'Closing" as the first 8 characters if it would be simpler

The Data Begins in Row 5 of Column A

My Current Macro is as below which I have adapted from another one I used

It is not working since it is detecting for the exact text "'Closing Balance" whereas the data registry would write "'Closing Balance as at 31/10/2009" , of which the "as at dd/mm/yyyy" portion would change every time a report is exported, but the first part "'Closing Balance" or even just the word "'Closing" will always be the same.

View 6 Replies View Related

Insert Text String From Inputbox In Cell?

Jan 13, 2012

I'm trying to write a bit of to take a text string collected from an inputbox, and paste it into a specific cell.

Should be easy but where my text string from the inputbox is "XYZ", when it enters it into the required cell it enters it as " ="XYZ" ".

Attempt at code is below -

Code:
Sub EmailEdit()
Dim Response As String
Response = Application.InputBox("Input administrator email address", , , , , , , 2)

'Check to see if Cancel was pressed.
If Response = "" Then

[Code] .....

View 2 Replies View Related

Insert Pic Into Comment For Every Cell That Has Text Containing String .jpg Or .png

May 10, 2009

I need a nudge in the right direction with how to amend the below code so that it :
1: cycles though all cells in a workbook, and sees whether the text reference of the cell contains a picture filename (i.e. searches for .png or .jpg in the cell contents)
3: if (1) is correct, it retrieves the picture from "C:/Users/jeff/Documents/Standards/" and pastes it as a cell comment background in the cell to the right (and overwrites any comment backgrounds that might already exist there)

The code below does something a bit different: it looks in a defined range, then adds a comment with a background picture retrieved based on the text in the cell to the left.

I'm sure this is a pretty basic change, but my VBA skills aren't up to it...I've only just started reading though Walkenbach's Power Programming! I'm using Excel 2007

Sub InsertComment()
'www.contextures.comxlcomments03.html
Dim rngList As Range
Dim c As Range
Dim cmt As Comment
Dim strPic As String

On Error Resume Next

Set rngList = Range("A1:A5")
strPic = "C:/Users/jeff/Documents/Standards/"
On Error Resume Next
For Each c In rngList
With c.Offset(0, 1)...................

View 9 Replies View Related

Insert Multiple Characters Into String With LEFT RIGHT Functions?

Apr 29, 2014

I've got a long list of account numbers of varying length and ultimately need to add a dash and a dot into these numbers. Here's an example

12345678 to> 1-2345.678
123456789 to> 12-3456.789
1234567890 to> 123-4567.890

The standard format is always 3 numbers after the . and 4 numbers between the - and .

I can get to this by doing a series of functions starting with this:

=LEFT(A1,LEN(A1)-3)&"."&RIGHT(A1,3) to get 12345.678 or 123456.789

copy/pasting that value into another field and then doing this

=LEFT(E1,LEN(E1)-8)&"-"&RIGHT(E1,8) to get the results above

I can't quite figure out the format to combine the multiple steps/functions into one so that I'm not copy/pasting values and re-doing the function.

View 7 Replies View Related

Insert Column Macro Based Variable String Letter

Jun 11, 2008

I'm working on some code that's part of a userform. To illustrate what I need, I will give an example. A column letter, 'J' for example, is stored in colNum.Value taken from the userform. I need both a column inserted before column J, and data entered into that new column in row 2 (thus J2, which would now be blank).

View 4 Replies View Related

Insert String In Another Cell When Entered In Cell

Mar 20, 2014

I have a protected sheet. When someone enters a value in column A, I want a value to be inserted into the corresponding row in column G.

E.g. When I enter date in cell A45, I want the time this date was entered into cell G45

View 6 Replies View Related

Utilize A Select Case Statement In Target Intersect Statement

Jul 28, 2009

I am currently using an Intersect statement in a worksheet module to perform two things:
1. Insert a time stamp into row 2 when row 1 has a price inserted
2.To clear that time stamp if the price is deleted at some later date.

My problem is with the time stamp value being deleted by the user.
If I try to clear the price (now that the time cell =empty) I get a Runtime error 91 - Object Variable or With block variable not set.

I would like to convert this code to a select case statement but I'm not sure how to do this in this situation. Would error coding be appropriate in this instance?

View 5 Replies View Related

IF/Then/Else Syntax

Nov 25, 2008

Im having problem with If/Then/Else Statements Not sure what the problem is. I have a text box a user inputs whatever in to create two new tabs one is the tab name then the next is tab completed. It isnt liking my syntax I have here.

View 7 Replies View Related

Creating New Tab Syntax

Nov 25, 2008

if I copy and Add it created a new workbook not the current on im in. What am I doing wrong?

View 5 Replies View Related

Getting A Syntax Error

Jan 28, 2009

Getting a syntax error which and I'm not sure what I've done wrong. This is the bit thats causing the problem:

View 2 Replies View Related

Looking For Correct Syntax

Feb 10, 2009

trying to write a formula in a VB to some cells:

View 3 Replies View Related

Syntax Error

Aug 27, 2009

what is wrong with this syntax:

View 2 Replies View Related







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