Concatenate Query; Include 2 Variables With 1 Constant Entry

Jan 23, 2009

I have recently discovered the concatenate formula. Is it possible to include 2 variables with 1 constant entry. i.e. I wish to establish file names with 2 variables, e.g. 0051 and 1025 followed by a file extension, .EC0 - the file name being 00511025.EC0. The variables are in Excel columns. I can achieve the wanted result by establishing an extra column and copying .EC0 into every cell of that column; but I would prefer not to have that extra column in the completed Excel file.

View 2 Replies


Constant Variables - Declare Name In Separate UserForm As Constant

May 8, 2012

I have a userForm (Form1) that contains a persons name that I would like to reference in a separate UserForm (Form2). In the separate UserForm (Form2) I need to reference this persons name many times, so I was wondering if there was a was to declare this name in the separate UserForm (Form2) as a constant. Only thing is that a constant, to the best of my knowledge, must be an expression and not a variable. Mainly, I'm trying to avoid declaring the myName variable in each Sub within Form2, which it will be needed for a ton of Sub's.

Code for Form2: Const myName As String = Form1.txtName.Value

View 5 Replies View Related

Multiple Variables To Equal Constant

Mar 11, 2007

I'm trying to create a spreadsheet that will tell the user how much of two products to use in order to get a desired percentage of total fat. For example: if the total pounds is to be 3000 at a fat percentage of 30%. There would be two products (a lean meat and high fat meat). The percentage of fat in those two meats will change each time. The user will enter those percentages and then the formula needs to tell how many lbs of each to use in order to get the desired percentage of fat.

Each time there is a "target" amount of each product to use. For example: 1000 lbs of Lean Meant and 2000 lbs of Fat meat. Those are based on the ideal levels of fat in each.

However, sometimes the fat is not at the ideal so the formula has to be adjusted in order to compensate.

View 9 Replies View Related

Concatenate To Include Carriage Return

Nov 12, 2008

Name Address City State Number etc. I would like to concatenate these and include carriage returns so that the result looks like this:


I'm currently using the following #

View 4 Replies View Related

Look Up And Match Several Values To Include Entry

Feb 9, 2010

I want to be able to look up and match several values in my worksheet to confirm, that an entry is valid. I would like the test to return an OK / NOT_OK value at the end of each entry. In the attached file you will find four different variables. In order to include an entry, it must be matched to find related entries, before it can be included.

For an entry to be OK, it must contain a company that has been listed in the same industry, in four different quarters (not necessarily in the same year). A company can be listed in several industries, but as long as there are not four entries in the same industry in four different quarters, the entries are not yet OK (hence NOT_OK). Therefore the only entries that are valid in the attached file are:

Company A Q1 2009 Administration OK
Company A Q2 2009 Administration OK
Company A Q3 2009 Administration OK
Company A Q4 2009 Administration OK

I work in XL 2007, but have converted the attachment to 2003 for maximum compatibility.

View 3 Replies View Related

Concatenate Macro With Variables

Aug 11, 2008

I'm trying to write a macro concatenate a report but the report outputs data on different rows when ideally I'd like it to all be on one row. The C column will have something in on the top line but blank for the rest until it gets to the next set of data.

Unfortunately it's not always the same amount of lines every time.

View 9 Replies View Related

Variables And Constants Query

Oct 13, 2006

how do you assign a value to the variable to make it available to the module?

I have tried the following without success

Option Explicit
Dim batch As String
Dim folder As String
Dim title As String

batch = Sheets("Day 1"). Range("D1")
folder = "M:CR dim checksRetention"
title = folder & batch
file = title & ".xls"

View 9 Replies View Related

Set Variables To Each Entry In Listbox

Jun 7, 2008

I have a listbox that has one or more (up to five) entries. each of these entries must then be represented by a variable. i set up msgboxes to make sure the right values are collected. but im getting back blanks!

With UserForm1.Booths
For fLoop = 0 To .ListCount - 1
If .Selected(fLoop) Then
fUpper = fUpper + 1: Redim Preserve Boothlist(1 To fUpper)
Boothlist(fUpper) = .List(fLoop, 0)
End If
Next fLoop
For i = LBound(Boothlist) To UBound(Boothlist)
If i = 1 Then
Boothlist(i) = group1
MsgBox (group1)...................

View 5 Replies View Related

Query Tables DataTypes And ColumnWidths Arrays As Variables

Jul 12, 2011

How do I set the datatypes and columnwidths as variables? I get invalid procedure call or argument. Will import different text files and set dt and cw based on names.

Dim dt As Variant
Dim cw As Variant
dt = "2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2"
cw = "7, 7, 5, 2, 7, 16, 3, 15, 11, 9, 9, 9, 7, 3, 3"
With ActiveSheet.QueryTables.Add(Connection:="TEXT;B:BOMOEBTRfro1.txt", Destination:=Range("A1"))

[Code] ........

View 8 Replies View Related

Editing Macro To Include Multiple Rows And Include Text Formatting For Leading Zeros

Apr 2, 2014

I need the values that are copied from the template to copy over in text form from the "Data" Tab. Secondly, the master sheet has multiple lines for each vendor. For the area highlighted in red I'd like for it to copy all cells in column C for the vendor and search the vendor by name. Then, move to the next sheet.

View 10 Replies View Related

Concatenate Duplicates: Concatenate Results Of All Equal P/N's From Any Given List

Oct 6, 2007

I have a list of P/N's that are used in more then one location. and it's sorted by P/N's.


I Want to be able to put in Col A the concatenate results of all equal P/N's from any given list. Or at least select the few cells that i know are duplicates and from that copy the Location to a single Column.

ColA ColB__ColC

View 5 Replies View Related

Concatenate Non Blank Cells But Use Concatenate And Substitute Instead Of IF

Aug 11, 2013

Sampling table :


Desired results obtained via IF =IF(B2>0,A2&" , ",A2)&IF(C2>0,B2&" , ",B2)&IF(D2>0,C2&" , ",C2)&IF(D2>0,D2,"")

one , two , three , four
one , two , three
one , two

Is there any smarter, shorter formula via Concatenate and Substitute or other formulas ?

My closest match, but not good enaugh is =SUBSTITUTE(CONCATENATE(A2&", "&B2&", "&C2&", "&D2), ", , ", " ")
[ returna 2 commad ]
one, two, three, four
one, two, three,
one, two
one ,

View 9 Replies View Related

Getting Entry In One Cell To Cause Entry In Another Based On Entry In 3rd

Mar 20, 2014

I have a percentage in R3.

If I make an entry in D13 then I want the R3 to be duplicated into C27 otherwise C27 should be 0.

View 4 Replies View Related

Pass Parameter From Excel Through MS Query To MS Access Query

Nov 26, 2012

I have an MS Access query that contains a parameter. The parameter is a date field, and I have configured that in the Access query. If I run the query within the MS Access user interface, it prompts me for the paramater value as expected, and runs just fine. However, I want to connect to this query from within Excel as a data source.

I have created a connection to the Access file using ODBC from within Excel. In the MS Query window, I am merely selecting all of the fields resident in the MS Access query, and returning all values. In other words, there is no selection criteria in the MS Query. I have done this many times with Access queries that DO NOT contain a parameter, and everything works fine. However, in this instance, I need to pass a parameter through to MS Access in order for the query to run. At the moment, I get the "Too Few Paramaters...1 expected" error message. This makes sense, because I haven't figured out how to pass the paramater to MS Access.

Is there a way to structure this that does not involve VB code? If so, I'd love to know how. I have tried creating parameters in MS-Query with the same name, but although I get the prompt it doesn't connect with the Access query as the source for the parameter value.

If the solution requires using code, I'm good with VB Code in there VB for Excel code that could make this happen?

Failing that, I guess there must be (I've seen a few in my search thus far) Access VB Code that can make this work. I'm very rusty using VB with Access, so this is my least favored solution. However, if this is the only option, keep in mind that I need to pass the paramater ultimately from a user who will initiate the process using Excel.

View 3 Replies View Related

Query Parameters Which Takes The Date From The Cell Into The Query

Mar 29, 2007

Need the query parameters which takes the date from the cell into the query. How should I modify my query if it needs to take the date from a cell?? The bold one date should be picked from one of the cell in sheet 2.

My query is this


View 2 Replies View Related

Unzip Code - Works Without Variables, Breaks With Variables...

Feb 5, 2009

Unzip Code - Works without Variables, Breaks with Variables.... This has been driving me bananas...

I have the

View 2 Replies View Related

Create An Conection With MS QUERY To A Csv And Query Data

Sep 6, 2008

I have a csv file on another drive on the network that i need to query. I believe that ms query would be the best way. I know that a DSN needs to be setup but this macro will be used by various users who wont know how to do that. thus I would like to create one via VBA every time the task needs to be run.
I haven't a clue how to do this and i need it to be explained to me in general terms with words of one syllable!

View 9 Replies View Related

Check If Any Entry In List Older Than 7 Days - Delete Second Double Entry?

Sep 11, 2013

I am having a sheet where I keep track of when online surveys have been sent to users. The users enail address (column K) may be on the list for several times, but I need to make sure that there is at least a 7 days pause between sending the first mail and the second, depending on the visit date (column G).If there are less than 7 days between two entries with the same email address, the user is not qualified for taking another survey.

I was thinking to write an IF formula which returns either 1 or 0 and then let conditional formatting highlight and HIDE the row via a VBA loop.

View 3 Replies View Related

Formula To Look For A Number Entry On One Worksheet And Return Another Fixed Entry?

Mar 14, 2014

Is there a formula that will allow me to look for the existence of any number value in a row of one worksheet and then return a specific number value in a cell on another worksheet? For example, if the formula finds any number value it will always return the number 15 to a cell on another worksheet.

View 3 Replies View Related

Tranferring Variable Entry From Form Entry To Macro

May 1, 2009

I used a form with textboxes for data input for one of my vb macros. Currently I have the private sub from the form transfer those entries to a remote cell on the spreadsheet, (like in column "HZ"), so that the macro that will actually utilize them can retrieve them. Is there a way to pass that data directly from what is entered in the form in the textboxes to the macro that will actually use them?

View 4 Replies View Related

Query An Access Query With VBA Function

Jul 8, 2008

I'm trying to query a query in Access 2003, from Excel 2003.

The query in Access looks like:
AccessQuery: [SELECT VBAFunction(field1) FROM Table]

The query in Excel looks like:
ExcelQuery: [SELECT * FROM AccessQuery]

I use the following VBA code in Excel to excecute the query:

With ThisWorkbook.Worksheets(cDataSheetName).QueryTables.Add(Connection:=strConnection, _
Destination:=ThisWorkbook.Worksheets(cDataSheetName).Range("A1"), Sql:=strQuery)
.RowNumbers = True
.Refresh BackgroundQuery:=False
iResultRowCount = .ResultRange.Rows.Count
End With
When I execute this code I get the error message 'SQL Syntax Error' (Error 1004). When I remove the VBA function from the query in Access, it all works fine.

View 9 Replies View Related

Edit Query Using Query Wizard

Apr 25, 2006

I created an Ms Excel Database Query to bring in data from MS Access. (versions 2002 of MS Excel and Ms Access). The query works fine initially. I can right click, choose Edit Query and change my criteria. Results are returned almost instantly.

My problem is that, once I save the workbook, or autosave happens, I get an error when I right-click to Edit Query: This query cannot be edited by the Query Wizard..

View 2 Replies View Related

Constant Array In Vba?

Jul 24, 2009

Is it possible to use a contstant array in Excel VBA? I have tried several differnt ways to declare it, but the VBA editor keeps yelling at me. The following did not work:

View 2 Replies View Related

If And Constant Logical

Jun 17, 2008

i have problem with a long code.

So I divided that code in 4 parts.

So I have
module1 -> Sub Macro1()
module2 -> Sub Macro2()
module3 -> Sub Macro3()
module4 -> Sub Macro4()

I have declared

Option Explicit

Dim mdNextTime1 As Double
Dim myEnter As Integer
'Dim myK1 As Integer
'Dim myK2 As Integer
'Dim myK3 As Integer
Dim myK4 As Integer
Const myC As Integer = 82
Dim Lastrow As Integer
Dim myActualRow As Integer

all my sub have the following structure:

View 9 Replies View Related

Declare Constant

May 15, 2007

what does the symbol # means in VBA? (but I couldn't put the # in the subject of my message )

I'm trying to understand someone's code... at some point he wrote:
sum_LU_Area = 0#

I thought that the # was used to declare a constant but I'm not too sure because in his code earlier he declared
Public sum_LU_Area As Double

Beside, sum_LU_Area is calculated somewhere further in the program.

View 7 Replies View Related

Increment One Column But Keep The Other Constant

Jun 28, 2009

Here is what I try to do:
A1 = Power(C1,B1)
A2 = Power(C2, B1)
A3 = Power(C3, B1)
An = Power(Cn, B1)

How can I increment column C while keeping B constant at B1 by using right-bottom mouse pointer dragging on column A? Or is there another way?

View 2 Replies View Related

Copy Formula With 1 Constant Value?

Oct 6, 2011

If I have a formula that I want to copy throughout a column, but I want to keep one of the parts of the formula constant, is there a short way to do this?

For example:

First cell is A1*O24
Second Cell is A2*O24
Third Cell is A3*O24

Right now when I try to copy the formulas, the second cell always reverts to A2*O25, which is not correct.

View 2 Replies View Related

Using Variable Instead Of Constant In VBA Formula

Jan 8, 2012

I try to adjust chart range using variable value instead of constant value.

My current code :
ActiveSheet.ChartObjects("Chart 4").Activate
ActiveChart.SeriesCollection(1).Values = "='Check_2G'!$Z$2:$Z$32"

I need to make range ( "='Check_2G'!$Z$2:$Z$32" ) adjestable with varible like m instead of 32 in previous code.

View 2 Replies View Related

Macro Is Counting A Constant Twice?

Apr 25, 2012

For a single column of values, the following macro counts the first value twice, how can i fix this? for example: A1 thru A5 are a,a,b,c,d the result is

a 2
a 2
b 1
c 1
d 1

and should be
a 2
b 1
c 1
d 1


Sub CountOfEachItem()
Dim ListRange As Range
Dim NewList As Range


View 1 Replies View Related

Excel VBA - Using Constant For A Set Of Values

Oct 19, 2012

Const Books1 = Array("5638", "5639", "10984")
Const Books2 = Array("3140", "7509", "3050", "7549", "7508", "7032", "6770", "6773", "4388", "6460")

I want to set an array as above for multiple use in my code (Used for auto-filtering various data with the same criteria).

The above is not allowed it seems, is there an alternative method so that if my Constant changes I only need to amend this once.

View 6 Replies View Related

Copyrights 2005-15, All rights reserved