Using Substitute To Change Text In Variable?
Mar 25, 2014
I have a text variable MtgDate containing "25/03/2014"
I need to produce another text variable (to build into a file name) MtgDate1 with the text "2014-03-25"
I was going to use the Substitute function to replace the "/" with a "-" then Mid to juggle the dd-mm-yyyy to yyyy-mm-dd but at the moment I can't even find the right syntax for the Substitute.
Got as far as:
[Code] .....
but this just sets MtgDate1 as "=Substitute(MtgDate, " / ", " - ") ie reduces the double quotes to single ones.
View 2 Replies
ADVERTISEMENT
Jan 11, 2014
I would like to take a string such as R0-H6-D2 and return a number (1-4) based on one of four values for R, H and D in a separate column for each. The attachment should make what I am trying to do clearer.
Substitute.png‎
View 7 Replies
View Related
Feb 9, 2009
I have a text field (description) and in the description i have a product code S followed by 7 digits and then in the process of pasting into excel i have lost the space after this code and before the next text. E.g. "Ballpoint pen S1234567With Free Delivery" should be "Ballpoint pen S1234567 With Free Delivery".
I dont know how to say =if("S" followed by 7 numbers,subsitute ..... etc)
I understand how to use IF and substitute. its the 7 numbers part i am stuck on.
I could do it in access with the wildcards but excel is different.
View 14 Replies
View Related
Feb 11, 2005
I'd like to use VBA to create a super substitute function. For my needs, nesting is insufficient because my substitution list is at 20 and growing. To make matters worse, the function needs to be used in several places.
What I'd like to do is have a named table with two columns for the function to use as a look-up for potential substitutions. The first column would contain the original text and the second would contain the replacement text. This way, whenever new items come up, all I have to do is add them to the list. The syntax of the function would be along the lines of SUPERSUB(TextString, table), where TextString contains the text that could be modified.
View 9 Replies
View Related
Oct 2, 2012
I've in cell A1 an entry like this: 123*456*7890
=SUBSTITUTE(A1) in cell 'A2' gives me: 1234567890
=ISTEXT(A2) in cell 'A3' gives me: TRUE
BUT, =A3+1 gives me: 1234567891
Hows that happening? Substitute function gives me the output which is a TEXT, and how is it that when I add 1 to it, I get an answer? Shouldn't I get a #VALUE! error instead?
View 2 Replies
View Related
Aug 18, 2014
In cell A1, I have three possible text strings: (1) "change/s: changed the color green to red", (2) "change/s: changed the color from green to red, changed the size from big to small", or (3) "changed the color from red to green".
I need a formula that will look at a text string, and if there is a comma in the string, it finds the word "change/s" and substitutes it with "changes", if it finds no comma, it substitutes the word "change/s" to "change", and if the word "change/s" is not in the text string at all, it leaves that text string unchanged.
View 4 Replies
View Related
Jan 10, 2014
i have some existing code which is trigerred when anything is input into column c. The code then adds various information in another three columns. One of which pastes a vlookup formulae, and i would like this forumlae pasted into the column c cell which i initialy edited, in order to remove the requirement for one additional column.
The existing code i have is:
Code:
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Dim MyText As String
MyText = Environ("username")
If Target.Cells.Column = 3 Then
With Target
If .Value "" Then
.Offset(0, 2).Formula = "=VLOOKUP(D:D,'P:TAOffshoreTAOffshoreTreasuryRecsGeneralCommit ID''s for control Sheet - Do not move or delete[commit ids - DO NOT DELETE OR MOVE.xls]Sheet1'!$A$1:$B$65536,2,0)"
[code].....
I have tried changing the offset to (0,0) or changing the offset to 'target = ', which does add in the vlookup but then the macro debugs at the 'If .Value "" Then' code?
View 3 Replies
View Related
Apr 22, 2006
I am using the following code.
Function VLOOKUPRow()
Dim SV As Object
Worksheets("Midterm").Select
SV = "UserFormAppend.TextBoxInvID.Text"
VRow = Columns(1). Find(What:="SV", After:= Cells(1, 1), _
LookIn:=xlValues, SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious, MatchCase:=True).Row
MsgBox (VRow)
End Function
I am trying to give the VRow variable the value or the row that The text from my text box is on. How do you make the what look for a variable. I can get it to work if i put a constent in the what. how to make the what look for a variable. I'm new at this forum thing so im not quite sure how to check for answers.
View 2 Replies
View Related
May 12, 2009
I am trying to change the variable value in my following code through array.
What I want is that both the statements
Debug.Print testarray(0) & "........" & testarray(1) & "......." & testarray(2)
Debug.Print custname & "........" & custaccount & "......." & worthcredit & vbCrLf & vbCrLf
should deliver me the same values i.e changedname 123456 and true
for testarray(0),testarray(1),testarray(2) i am getting the values but I am not able to change the variable values for custname ,custaccount and worthcredit, although I am accessing the same elements.
Here is full ....
View 12 Replies
View Related
May 25, 2011
Trying to create a VBA code that will enter a formula such as: SUM(Q2:Qt) in a range of cells for instance A2:A10, and if there is information in B11, B12, B13 etc., it will consider that variable change and enter the SUM formula in cells A11, A12, A13.
I read that Dim LR As Long is the solution but not sure how to apply.
View 9 Replies
View Related
Jul 30, 2014
I have a list of Variables Dimentioned a Range. They are sequential; ie. A1, A2, A3, etc. How do I write the code to switch between these? Obviously, I am looking to correctly reference ("A" & aNo).
Code:
For Test = 1 To aNo
With ("A" & aNo)
.Select
[Code]......
View 9 Replies
View Related
Jun 22, 2009
I am trying to write a formula in code that uses a changing variable, "X".
here is what i have thus far:
.Value = (("G" & X) + 1) - (("G" & X) + ("B" & X))
I am trying to say: ((GX)+1)-((GX+BX))
X is a variable that changes in a loop. it will be the row number. so for example:
(G18+1)-(G18+B18)....I AM APPEARNTLY NOT WRITING THE SYNTAX CORRECTLY.
View 9 Replies
View Related
Feb 19, 2009
How do I change the selection of a range with a variable, and not a hardcoded number in XL2003? I have to update a set of spreadsheets every month, and it's a hassle to have to constantly open my pivot table worksheet, copy, open the summary worksheet, paste ... etc. etc. etc. The code below is my attempt at creating a ComboBox with "January, February, March, etc." and every time I select a particular month it will automatically copy data from my pivot table worksheet into my new summary worksheet in the correct column. The range of data from my pivot table worksheet will never change, so I have no problem hardcoding that in, but based on which month is selected will alter which column the data goes in in the new worksheet.
I want to be able to write code for one month (say January) and then when I want to use a different month (say February) I can just change the column number and call up the originial January code.
Private Sub ComboBox1_Change()
Dim ColNum ' This is the variable I want to change based on which month is called
If ComboBox1.Value = "January" Then
ColNum = 1 ' Column number for Column A- Where my January column is
Elseif ComboBox1.Value = "February" Then
ColNum = 2 ' Column number for Column B- Where my February column is
' etc. etc. for each month
End If
Call January
End Sub
Private Sub January()
' Just a quick msgbox to make sure the previous macro is calling this one
MsgBox "Is this macro running?", vbQuestion + vbYesNo, "Check"...........................
View 9 Replies
View Related
Oct 19, 2009
I need the variable here to be 7 numbers long, if not I need to add zero's at the beginning. However the problem I am having here is when then number of zero's is greater than one.
Basically in the example below the variable 'Zeros' = 2 so I need the variable 'Variable' to give answer "00", how do I do this!?
If Len(ActiveCell.Value) 7 Then
Zeros = 7 - Len(ActiveCell.Value)
Variable = CStr("0") * Zeros
End If
View 10 Replies
View Related
Oct 22, 2013
I have goal table on table A and i want it data fill to tabel B with constant sum. maybe like sudoku in sum right and sum down.
View 1 Replies
View Related
Sep 24, 2009
it's possible to change the caption (title) of a user form to a variable or cell value? Or does it always have to be hard coded?
View 2 Replies
View Related
Apr 25, 2009
This is the code on the Worsheet Change event.
Private Sub Worksheet_Change(ByVal Target As Range)
'Define the RMAs range and count the RMA ships. Do the rest of the math based on this calculation.
Dim intRMACol As Integer, EndRow As Integer, intRMAShips As Integer, LastCol As Integer
Dim rngRMAs As Range, rngHome As Range, sht As Worksheet
Set sht = ActiveWorkbook.ActiveSheet
Set rngHome = Cells(1, 1)
rngHome.Activate
EndRow = Range("A" & Rows.Count).End(xlUp).Row
intRMACol = Cells.Find(What:="RMA#", After:=[A1], SearchOrder:=xlByColumns, SearchDirection:=xlNext).Column
Set rngRMAs = rngHome.Offset(1, intRMACol - 1).Resize(EndRow, 1)
rngRMAs.Name = "RMAs"
rngHome.Offset(1, LastCol + 1).Formula = "=COUNTA(RMAs)"
intRMAShips = rngHome.Offset(1, LastCol + 1).Value
rngHome.Offset(1, LastCol).Formula = "=SUM((" & EndRow & " - 1) - " & intRMAShips & ")"
rngHome.Offset(1, LastCol + 2).FormulaR1C1 = "=SUM(RC[-2] : RC[-1])"
End Sub
I keep getting stuck when trying to set the intRMACol variable. I have used the code all over the place, but only in Modules, never on a worksheet event. EndRow, the variable right above, sets correctly so I don't get it.
Also, do I have to define the Target variable? Am I going about this the right way? I basically want three formulas to calculate when any change is made on the worksheet.
View 9 Replies
View Related
Feb 25, 2013
How do you drag down a column to auto complete the values but do it so it only changes one variable.For example, I am dragging down =IF(ROW()<=$B$5,MOD(E2*B1,B2),"") down my column.
However, I only want the "E2" value to change, and I want B1 and B2 to stay that way. Whenever I drag down, it keeps changing all three values. Is there a way to make it just so that the E2 value is the only one that changes?
View 4 Replies
View Related
Jan 12, 2010
I'm trying to make a spreadsheet more secure to prevent unintentional changes.
What i'm looking to do is to lock all cells so no changescan be made unless the user clicks column A.
Column A should remain unlocked and when the user clicks it i would like the entire row unlocked for editing (but only after column A is clicked)
I will run a macro to lock the entire sheet again once the sheet closes.
View 2 Replies
View Related
Oct 2, 2008
I have data coes that need to be converted, basically need to remove 1st and 12th digits, 12th digit only, or 11th digit. I have built spreadsheet with a mid sub formula to do all 3 separately, but cannot figure out how to combine the formula to do all three.
I am attaching the spreadsheet,
View 11 Replies
View Related
Feb 7, 2012
Following code doesn't work
Dim Value1 as String
Value1 = Substitute(Cells(4, "A"), "A", "", 1)
correct syntax to assign the result of substitute function in the Variable?
View 3 Replies
View Related
Oct 2, 2007
I have a formula who examines if they are the same. The last number at first quote is not a problem (the 45). The problem occurs only and allways when the horse's name have an apostrophe " ' ". In first example apostrophe is different from the usual. If i manually delay it and replace it - type keyboards apostrophe everything works fine. Obviusly its a symbol. I want a small macro for replacing all these symbols at, lets say column B ,where these names are located. How can i use substitute at this case?
View 9 Replies
View Related
Aug 19, 2008
I understand that Microsoft left Filesearch out of VBA for Office 2007 because it was buggy. Sadly, however, I still need it. It would help if they placed a comment in VBA help that stated that it was discontinued. As it is, they make it look as if Filesearch is still available for use. OK, I'll stop ranting now.
After reading several earlier posts, apparently I have to use the Dir function. I learn visually and cannot figure out how to use the function from the VBA help file since there are no examples. Does anyone have an example of code using the Dir function to insert filenames in an array? Once I see the code, I'm sure I can adapt it to suit my needs.
View 9 Replies
View Related
Nov 6, 2008
I have a whole bunch of dates in a column that look like this:
5/01/1998 when it is supposed to be 5/01/2098 is there a character you can use when you are using the find & replace mode i.e.
*/**/20** I know it is not the asterisk but I believe there is some other character that can be used and it won't change any other of the numbers except the 19. I tried reformating and it does'nt work because of the way they sent it to me.
View 9 Replies
View Related
Oct 19, 2009
Is there a way to use something like the SUBSTITUTE function when sending an e-mail using a macro?
Here's my ....
View 9 Replies
View Related
Feb 15, 2012
I am acquiring multiple spreadsheets that do not always match row number, due to additional information on some sheets. By this I mean that the information may be in row 31 on on sheet and row 39 on another, the column location is the same each time. I have been trying Vlookups, indexing and matching plus combination formulas - with no luck. How to get the information I need with a moving cell reference?
I can provide a small copy of a workbook, if needed.
View 1 Replies
View Related
May 17, 2014
I've got a workbook with multiple formulas e.g.:
t=S/D
TCh=Ch*(S^2/2D)*(D/Q)
TC=Co*(D/Q)+Ch*((i-D)/2i)*Q
which are built by implementing the symbols:
t, S, D, TCh, Ch, Q, TC, Co, i
I'd like to substitute the symbols in following sequence:
t = a
S = b
D = c
TCh = d
Ch = e
Q = f
TC = g
Co = h
i = x
Questions:
1) Which formula could substitute the symbols automaticly?
2) If there are up to 100 formulas in the workbook in the range of A1:E100,-
would it be possible to to set the substitution for the mentioned range?
See the attachment.
View 5 Replies
View Related
Jun 28, 2009
Adding second substitute command
What I need to do is change this:
View 2 Replies
View Related
Jul 10, 2007
I have numeric values in approx 1000 cell entries that I need to edit:
example cell C1 contains the value 00100300308W400
I'm trying to edit this entry to show 00/10-03-003-08W4/0.
I've tried using wildcards with the SUBSTITUTE function:
=SUBSTITUTE(C1,"***************","**/**-**-***-****/*")
View 10 Replies
View Related
Dec 10, 2008
I can choose among three variables in cell A1. They are named P10, P50 and P90. I want to substitute them with 1,2 and 3 in cell B1. Is it possible to do this by using the substitute-function or do I have to use a nested IF.
View 2 Replies
View Related
