Variable With Concatenate
Jan 2, 2013
I have a simple task and I use the concatenate formula to resolve alot of keystrokes. I now have an issue where I have to edit that formula because of a additional character for the string won't upload into a database. Here's my example:
1. Cell A1= 12345xxxx Cell B1= 67 Cell C1= 8
I use =CONCATENATE(A1,"-000"&B1,"-000"&C1) and my result is displayed in D1= 12345xxxx-00067-0008
Easy enough! Now adding the following is the problem:
1. Cell A2= 12345 Cell B2= 67 Cell C2= 89
I use =CONCATENATE(A2,"-000"&B2,"-000"&C2) and my result is displayed in D2= 12345xxxx-00067-00089
Now D2 has one too many characters from C2
I modify the formula by reducing to "-000"&C2 to be "-00"&C2 manually but now the line items have quadrupled and manually isn't going to work for this being so time consuming.
Is there a way to use a variable with the concatenate or am I using the wrong formula period ??
View 3 Replies
ADVERTISEMENT
Oct 24, 2012
I have a problem with a worksheet that my company accounting system exports every month.
Attached : sample of the worksheet.
In column ( F ) , I need a macro to do the following calculations:
1- Check for the Title - if it begins with "Cost Center"
2- Check for the Title - if it begins with "Account Code"
3- Detect the Range Start just below "Account Code" , and End with the row above "Total"
4- Concatenate the string written in each row of the range with the string in "Cost Center"
The Story is:
Each Month I've this worksheet with hundreds of Cost Centers and subsidiary Account Codes, And to be able to analyze the accounts efficiently I need to concatenate both Cost Centers & Account Codes manually ( as you see coloring sample in the attached file ). Which led to wasting many hours , and high risk of error while copying and pasting formulas.
View 3 Replies
View Related
Feb 28, 2008
Following advice from this forum I have developed the following code to save a workbook with a filename made up from Ranges within a worksheet. What I had also hoped to do was include the first 2 letters of a Range.
The code that works is as follows
View 14 Replies
View Related
Sep 15, 2014
I have two columns - A and B. In column A there are blank cells and filled cells. There is a blank cell and after it - a variable number of filled cells, then a blank cell again and a variable number of filled cells. I am trying to concatenate the filled cells from column A into a single cell in column B, which is corresponding to the blank cell in column A. Values should be comma delimited.
For example: A2-blank; A3-27; A4-63; A5-blank; A6-31; A7-blank .. and in column B should be B2-27,63; B3-blank;B4-blank; B5-31,B6-blanc and etc.
View 5 Replies
View Related
Dec 15, 2006
I have two sheets of data, exported by a program. The first sheet uses a single row for information of each log entry. The second page will have 1 to X rows for information of each log.
What I am would like to do is combine that data into one cell, and place it in the corresponding row on the first page. This will be used in a mail merge, and I would need commas and an "and" between the data points, where appropriate for proper English. I know how to get the data together, but am having issues trying to get the correct grammar within the cell.
Willing to use either VBA or other formulas.
View 9 Replies
View Related
Nov 14, 2007
I want to do a concatenate for multiple fields, but want the user to specify which cells should be concatenated by entering this in a few fields. What does this formula (or VBA) look like?
Let's assume there is the possibility to concatenate a maximum of 4 cells. Call the 3 cells that will be concatenated cell1, cell2, cell3 and cell4.
On the first sheet of the workbook the user enters the following:
cell1 = Sheet2!A4
cell2 = Sheet2!B4
cell3 = Sheet2!C4
cell4 = Sheet2!D4
Here are some cells and their values:
Sheet2!A4 = Hel
Sheet2!B4 = lo
Sheet2!C4 = World
Sheet2!D4 =
Upon enterring the information after the = sign for the cell1, cell2 and cell3, the formula for the concatenate becomes: =CONCATENATE(Sheet2!A4, Sheet2!B4,Sheet2!C4 ).
The result of the formula is: HelloWorld
If I now change the values of cell1, cell2, cell3, and cell4 the following should happen:
cell1 = Sheet2!B4
cell2 = Sheet2!C4
cell3 =
cell4 =
The formula for the concatenate becomes: =CONCATENATE(Sheet2!B4, Sheet2!C4).
The result of the formula is: loWorld
View 9 Replies
View Related
Nov 23, 2006
I need to write a line of code which puts the following into a cell:
=+C4 & " - " & 1
The cell reference at the beginning will always be C4, however the "1" at the end will be variable named "GroupNo"
So my code will be something like:
Range("B46").Value = "=+$C$4 & " & "" - "" & "GroupNo"
This isn't working. I've tried every variation of what it should be but I keep getting errors like "type mismatch" or just the wrong thing being put in the cell.
View 7 Replies
View Related
Jul 23, 2014
I have a table that looks like:
7300
3
7301
2
7302
5
And I am trying to make a macro that will go concatenate the first 3 columns of all rows in worksheet 7300, the first two columns of all rows in worksheets 7301, etc, and insert them into column A of each corresponding worksheet.
View 7 Replies
View Related
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.
ColA__ColB__ColC
______Loc___PN
______1_____A
______2_____A
______3_____B
______4_____C
______5_____C
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
______Loc__PN
1,2____1___A
_______2___A
_______3___B
4,5____4___C
_______5___C
View 5 Replies
View Related
Aug 11, 2013
Sampling table :
one
two
three
four
one
two
three
one
two
one
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
one
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
Jun 4, 2007
I have the following code (just pasting the relevant section) which crashes when it reaches the highlighted line of code. and a dialog box pops up with the text: "Object variable or With block variable not set"
Sub test()
Dim StartRng As Range
Dim Buffer As Range
Set StartRng = WorkSheets("Sheet1"),Cells(1,1)
StartRng.Activate
ActiveCell. CurrentRegion.Select
Buffer = rngStart.CurrentRegion.Copy
' I also tried the following line of code but that didn't work either
'Set Buffer = rngStart.CurrentRegion.Copy
..
...
End Sub
View 9 Replies
View Related
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
Feb 20, 2009
I'm fairly new to macro's and VBA, by searching on the internet i've copied and pasted some code together into a macro.
But it ends in a Run-time error 91...
The macro opens a target .xls file in a selected folder, performs copy - paste actions from masterfile to targetfile.
Than it filters data in the targetfile sheet1 and copy's the results to the various other sheets; saves and closes the targetfile.
The next target file in the folder is opened and the actions are repeated in this second target file.
For the first target file this works smoothly; but for the second one (of a total of around 100) it does not copy the filter results to the other sheets in this workbook.
The error message i get is: "Run-time error 91:Object variable or with block variable not set."
When i hit debug it highlights the line "ActiveSheet.Next.Select" which, at least in the first file, seems ok.
View 9 Replies
View Related
Jun 27, 2014
I need a macro that will create a sheet at the end of the workbook.
Sum data from a variable amount of sheets and display that data on the created sheet.
Here is a step by step:
Starting on sheet 5.
Column D has a variable amount of part numbers in it. These part numbers would be different between the ascending sheets.
Column T, U, V has an inputed number in it that would need added up across all duplicate part numbers in all the sheets.
(Note: The data would also need started on row 4. Everything above row 4 is headers)
Here is a small example:
D E T U V
13019090W Part A1
68705500 Part B1
64202900 Part C-11
59634600 Part D1
26005300W Part E1
I need the macro to start with sheet #5(starting on row 4). Check to see if there is data in column T, U or V. If there is, to create a new sheet at the end. And copy the entire line into that sheet (starting on row 4).
After that, to check every sheet after (excluding the newly created one, starting on row 4) for data in Column T, U and V. And then check for duplicates in Column D on the newly created sheet. If there is a duplicate to add/subtract that number in Column T, U and V to the SUM in column T, U and V in the newly created sheet. If there is no duplicate, to copy the entire line to the new sheet.
So that when finished. On the new sheet, you have the SUM of T, U and V for everything that has data in T, U or V for all of the previous sheets, plus the entire line of the first instance (excluding the first 4 sheets).
View 2 Replies
View Related
Dec 7, 2008
I am trying to develope a "goto" page macro where the page value maybe 1,34,7A, 256C etc. I am not clear on how an inputbox value can be compared to a string variable or a numeric variable at the same time. This is what I have done, but when the texboxvalue is "7A" it doesn't work.
View 3 Replies
View Related
Feb 28, 2013
I Wrote a code which as intended to open each excel file in a folder and copy the data containing in it into a new sheet.
But While running the code the first excel file gets open, and an error message "Run Time Error 91-Object Variable Or With block Variable not set Error"
How to set the file which got opened from the folder to wbk variable.
Code:
Sub dataintoonesheet()
Dim i As Integer
Dim jk As Integer
Dim j As Integer
Dim rowstart As Integer
rowstart = 3
[Code] .......
View 9 Replies
View Related
Jul 7, 2009
When i try to run the code below i get the error message - object variable or with block variable not set-
Sub REFRESHXX()
'LIST
Cells(Sheets("POINTS").Range("DD801").Value, Sheets("POINTS").Range("DD800").Value).Select
Selection.AutoFilter Field:=1, Criteria1:="1"
'SET RANGE
Dim sFormula1 As String
Dim sFormula2 As String
Dim sCell1 As String
Dim sCell2 As String
Dim sSheet1 As String
Dim sSheet2 As String
Dim r As Range
Dim MyRange As Range 'for testing
With Sheets("Points")
sFormula1 = .Range("CY1").Formula
sFormula2 = .Range("CY2").Formula
End With
'FORMULA IN R1C1 STYLE
strFormula = "=IF(ISNA(VLOOKUP(RC[-1],MASTER!R4C3:R17908C7,3,FALSE)),0,VLOOKUP(RC[-1],MASTER!R4C3:R17908C7,3,FALSE))"
'ENTER FORMULA IN ALL CELL RANGES
r.FormulaR1C1 = strFormula
'REDUCE TO VALUES
Dim ar As Range 'an area is a range
For Each ar In r.Areas 'areas are discrete, contiguous ranges of cells
ar.Value = ar.Value
Next ar
'UNLIST
Cells(Sheets("POINTS").Range("DD801").Value, Sheets("POINTS").Range("DD800").Value).Select
Selection.AutoFilter Field:=1
End Sub
View 9 Replies
View Related
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
Nov 21, 2006
I found this nice little bit of code for a date range search in column A but it will not work. Apparently i have not set a variable or something.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim startDate As String
Dim stopDate As String
Dim startRow As Integer
Dim stopRow As Integer
startDate = InputBox("Enter the Start Date: (dd/mm/yyyy)")
If startDate = "" Then End
stopDate = InputBox("Enter the Stop Date: (dd/mm/yyyy)")
If stopDate = "" Then End
startDate = Format(startDate, "dd/mm/yyyy")
stopDate = Format(stopDate, "dd/mm/yyyy")
startRow = Worksheets("sheet1").Columns("A").Find(startDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
stopRow = Worksheets("sheet1").Columns("A").Find(stopDate, _
LookIn:=xlValues, lookat:=xlWhole).Row
Worksheets("Sheet1").Range("A" & startRow & ":A" & stopRow).Select
End Sub
View 9 Replies
View Related
Mar 24, 2014
I am getting error in Set MyRange
[Code] .....
View 3 Replies
View Related
Nov 19, 2008
to assign a variable to equal a Constant variable, then I need to find the last unused row on the worksheet, then paste that variable down the column (1-12200 or so rows). I also need to assign Strings for the first two Rows in the target column.
View 14 Replies
View Related
Mar 12, 2009
I attempted to modify "macro_1a1ay" to look into the "comments" sheet (column a) for a specific text string. If that text string is found, I have it delete the entire row, then re-sort the page and return to the calling page. It works well as long as it finds something. When it does not find the string (i.e. like now when the page is blank) it gives me the run-time error mentioned above.
View 7 Replies
View Related
Oct 7, 2009
I am having a lot of trouble finding out why I am getting error. I believe the error is because it can't find the number. In cells C115:C314 i have the numbers 1 to 200 in order. when someone types in 1 to 9 in the text box it works, but on 10 and over i get the error ???? here is the code I have
shCalculator.Range("C115:C314").Select
Selection.Find(what:=CInt(txtPackageID), After:=ActiveCell, LookIn:=xlValues, LookAt:= _
xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
, SearchFormat:=False).Select
x = ActiveCell.Row
shCalculator.Range("ProposedMeter").Value = Cells(x, 7).Value
shCalculator.Range("Package").Value = Cells(x, 12).Value
shCalculator.Range("ProposedMeterAmount").Value = Cells(x, 30).Value
shCalculator.Range("Term").Value = Cells(x, 62).Value
shCalculator.Range("Discount").Value = Cells(x, 67).Value
shCalculator.Range("Equipment").Value = Cells(x, 72).Value
View 3 Replies
View Related
Feb 16, 2012
I want the select case list of a ComboBox to be treated as a variable in order to shorten the code size. To clarify the problem, i post the code with what i want to do, but don't know how to do it that way.
Code:
Select Case ComboBox1.ListIndex
Case 0: y = "AT"
For j = 0 To 26
Case "j": y = sheets("name").Range("A(j)") 'Range A(j) is a string, so y as well, as seen in Case 0.
Next j
End Select
Is something like this possible?
View 3 Replies
View Related
Jul 11, 2014
I have two macros that run fine separately. I want to join them, but when I do I get an Object variable or With block variable not set error.
Here's the code
Code:
Sub border_highlight_insert()
' Change heavy border to new column, change tint - keyed to year A6
With ActiveSheet
Dim i As Integer
[Code]....
View 2 Replies
View Related
Feb 6, 2009
This works fine in Excel 07 but when ran in 03 it doesn't work and I get that error message.
Sub mcrRefresh2()
Sheets("WeeklyData").Range("A1").ListObject.QueryTable.Refresh BackgroundQuery:=False
Sheets("WeeklyData").Range("aa1").ListObject.QueryTable.Refresh BackgroundQuery:=False
Worksheets("WeeklyPivot").PivotTables("PivotTable1").PivotCache.Refresh
End Sub
View 9 Replies
View Related
Nov 9, 2009
I am getting an error at this line:
Set rng5 = Range(.Cells(celle2.Row, celle4.Column), .Cells(celle2.Row, celle4.Column))
Sub UpdateNumbers()
Dim rng1 As Range
Dim rng2 As Range
Dim rng3 As Range
Dim rng4 As Range
Dim celle1 As Range
Dim celle2 As Range
Dim celle3 As Range
Dim celle4 As Range
Dim celle5 As Range
Dim flag1 As Long
Dim flag2 As Long................
View 9 Replies
View Related
Aug 28, 2006
I have a spreadsheet form with all kind of values and what I want to do is to find a data in a worksheet named "Config" based on the spreadsheet activecell value. With the find row adress I return data of another column but same row (in the worksheet) in two diferent label captions. If the spreadsheet activecell value it was found in the worksheet everything is ok but if the value is not found I'm getting the message "Object variable or with block variable not set"....
View 7 Replies
View Related
Nov 10, 2006
Say you define a public range variable called Inputworksheet and you set it to refer to the worksheet called Inputworksheet. You have a separate string variable with the value Inputworksheet. How do you get this string variable value to call/control the range variable Inputworksheet?
I am getting an excel worksheet value from a lookup function that corresponds to the name of a VBA range variable. Once I have this worksheet value, I would like to use the range variable that has the same name as the worksheet value.
View 5 Replies
View Related
Jan 14, 2007
Function Find_Range(Find_Item As Variant, _
Search_Range As Range, _
Optional LookIn As Variant, _
Optional LookAt As Variant, _
Optional MatchCase As Boolean) As Range
Dim c As Range
If IsMissing(LookIn) Then LookIn = xlValues 'xlFormulas
If IsMissing(LookAt) Then LookAt = xlPart 'xlWhole
If IsMissing(MatchCase) Then MatchCase = False
With Search_Range
Set c = .Find( _
What:=Find_Item, _
LookIn:=LookIn, _
LookAt:=LookAt, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=MatchCase, _ ........................
Using the message box I see that sheet 1 opens but then I receive an error message Run Time Error 91, Object Variable or With Block Variable not set. I tried declaring and using set on "project" but got nowhere. I also need to have a message indicating project not found. Once this part is solved I will loop all of my other workbooks
View 5 Replies
View Related
