I have recently come across a VBA macro, which reads from a text box within an excel input sheet, copy the text content, and send it via email. However, when the text content started to go beyond a certain number of characters, the text that comes out of the email, got truncated. I presumed this is something to do with the limitation to the string variables where the string " messagebody$ " which is used to read the text can only take up a certain bytes.
I was looking for VBA help, and somehow, it mentioned something called a variable-length string. Would that be a solution to my problem, or how is that used ?
I am trying to to work out how to compare two string variables that I can see with my eyes are clearly a match but getting the right syntax to convince VBA they are the same is my issue.The first example is:
Direct Credit 158824 BRANDON DONNA C/ Lak30
the second example is:
Direct Credit 158824 BRANDON DONNA C/ 30 Lake Vue Parade
but that still would not get a match that I can find code for.
i am chasing the "holy grail" in VBA and I will leave the records that do not exactly match to manual processing although i hate to give up that easily
On to what brought me here; I have a spreadsheet with a column containing an address, eg '12 SMITH ST SOMESUBURB NSW 2001'. I'm looking at splitting this cell in half after the street and moving the second half (suburb state postcode) into the next cell, which i've managed to do with a series on InStr statements.
Now the fun part is that not all these addresses are streets, some are avenues (AV or AVE), roads (RD), place (PL), drive (DR or DRV) etc. Initially i just did this:
I made the formula work great, however I want to cut it down and simplify it and make it easier to manipulate... as opposed to having the filename written every so many times.. replace with a loop instead.... so:
Private Sub Weeklytestt_Click() Dim varCurDate As String If DateYesterday.Value = True Then Answer = "1" If DateToday.Value = True Then Answer = "2" If DateTyped.Value = True Then Answer = "3"
Select Case Answer Case Is = "1" varCurDate = Format(Date, "yyyymmdd") varCurDate = varCurDate - 1 Case Is = "2" varCurDate = Format(Date, "yyyymmdd")..........
I work with data that varies in row numbers but is consisten in column width. I am trying to write code that will create a named range for the data but be flexible to expand or contract based on the amount of data that is pulled in. Below is the
Const lngLastPossRow As Long = 65536 Dim strDataRng As String strDataRng = ActiveSheet.Name & "!R4C1:R" & Range("a" & lngLastPossRow).End(xlUp).Row & "C17" Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select ActiveWorkbook.Names.Add Name:=("Data"), RefersToR1C1:= _ strDataRng
While the result creates a named range called "Data" , it does not allow me to reference and data in any formulas ( sumif's, etc). Can someone tell me what I am missing. It appears to be returning the wrong data type (string as opposed to range values).
I've a big problem getting the Constraints in excel solver to use variables. It seems that the only accepted variable type in the contraints are STRING variables. THe following simple example works AS LONG AS the variable apa is a STRING value and as long as it doesn't contain decimals....
So the question is. How to use varialbes in the FormulaText argement other than string values as integers?
I have a lot of data that I need to have organized out but as far as I can tell an IF statement is the only thing that could work. I work at a casino and there are about 6,000 games that I need to be able to sort through. The games are classified by sections A - Q. Each section contains anywhere from 30 - 50 banks, and each bank contains anywhere from 4 - 20 games. This is what I am trying to accomplish.
Have a drop down that lists zones A - Q. If the user chooses A, for the next column to populate with a drop down of all of the banks in A. Then for the user to choose the bank they want and then have the next colum populate a drop down with the games in A>bank1. So basically...
Zone>Bank>Machine
then once you choose the machine, for the columns to the right to auto populate the information on that game such as themes ect.
Zone>Bank>Machine>Information on that machine
I have another sheet that contains the inventory of all of the games that I am using as my "database". I am able to get this working great with IF statements, but I obviously need to be able to use more than 8. Here is my current code...
I have a spreadsheet with a drop down box with 30 names in it. On the other worksheets of this file I have budget figures.
What I need is a correct formula so that when I select a name from the drop down box it will grab the figure from a cell in another sheet and place it into the sheet where the drop down box is.
Is it possible to use a formula which grabs more than one cell?
I am trying to take information from columns J ("Family Size") and L ("Annual Income"), and figure out how many units should be assigned to columns N ("Very Low Income Units") and O ("Low Income Units").
Essentially, this is what I want to do:
If J2 = 1 and N< 22850, then N2 = F2 and O2 = 0. If J2 = 2 and N< 26100, then N2 = F2 and O2 = 0. If J2 = 3 and N< 29400, then N2 = F2 and O2 = 0.
And so on through "8."
I looked at the Nested IF Limitation suggestions on the FAQs . . . it didn't seem like CHOOSE or VLOOKUP would get me where I need to go. Unless VLOOKUP is somehow merged with IF. Does anyone know the easiest way to get this done?
Steven Auto Merged Post;Oops . . . forgot to attach. See attached.
I have a nested IF statement that I am using in an application I am building in Excel. I am using logic to get the current month and then evaluate whether the value is a number. I would like to know if there is a better way to perform what I am trying to accomplish using another function. I am trying to cocantonate the the statements together which has worked in the past but not for this particular function.See Below... The function works if i use 7 IF statements.
i have tried to create a userform thru which data is to be entered. so that the data will be printed on a worksheet. here there are few text boxes, in which the number of digits should be equal to 14. after filling the userform when i click the print button if it is less than or more than 14 a pop up msgbox should be displayed with OK button and the cursor should go back to that particular text box. i have written the following code, but it has a problem. even if the total no of digits are 14 the msgbox is displayed.
say the text box name is Roll no
If txtRollNo.MaxLength 14 Then MsgBox "Roll No should be of 14 digits", vbInformation + vbOKOnly txtRollNo.SetFocus Exit Sub End If
I am working on Microsoft Office Standard Edition 2003 excel and am having trouble with my coniditional formats. I need to have 4 but can only have 3. Is there a simplae way around this? if not let me describe what i want to do and maybe there is a coded solution.
In columns N, R, V and Z i will have % increase in sales over a period. Below 0% would be red, between 0-5% is Amber, 5-10 is Green and over 10% is Purple. Is there a simple code that can be applied to this?
I can define the paramteres in excel in the row columns below.
I had a happy macro that copy and pasted sheets in an output folder for all excel files from a target file that included the desired changes. it worked fine. then i realized that when pasting a sheet, the text is limited to 250 characters, so an important portion of my sheet (some instructions in various cells) was not coming over. So i created the following which will copy the columns, but some of the row formatting is different and doesn't line up. my questions:
-is there a better way around this text limitation? -if not, is there a way to copy the row formats for this section and include in my macro? -is there a way to copy the cells (all or a range) themselves while using "ThisWorkbook", thereby including all the appropriate formating? it seems to not like to do that.
‘Copy columns from target sheet
Sub Perf_rev_subfolder_replace() 'Update the constant here if necessary Const sOutputFolder As String = "M:ODPerf review2010 oolsTest folder est" With Application . ScreenUpdating = False .DisplayAlerts = False .EnableEvents = False End With For Each fl In CreateObject("scripting.filesystemobject").GetFolder(sOutputFolder).Files If InStr(fl, "3.0") > 0 Then With Workbooks.Open(fl..............................
I have a workbook with two worksheets, Sheet1 and Sheet2. Sheet2 contains a table of values that need to be input into a cell on Sheet1, pending the results of comparing two other cells on Sheet1. I have 8 possible variations resulting from that comparison and I cannot make this work as the IF statement limits you to 7 deep.
Example:
Sheet1 A1 (text string value) = LOWER B1 (text string value) = L1 C1 (currency with no decimals) = Sheet2!Somecell (decision of which cell to use depends on combination of A1 and B1)
A1 can be either the string "LOWER" or "MIDDLE". B1 can be the strings "L1", "L2", "L3", or "L4". The strings in B1 are not cell references, but simple text. This leads to four variations for a row that has "LOWER" in it's A column, and the same for "MIDDLE" - totaling 8 possible combinations.
Depending on the combination, I need to input a number from Sheet2 and that number is different for each unique combination of the eight possibilities. There is no mathematical calculation taking place on Sheet2 - just an "if x and y then z" decision on Sheet1. I will use the value of Sheet1!C1 in other math functions on Sheet1.
how do i set the macro to read a range without limitation?
currently i have a recording macro that only reads to P126 - if i end there data in C127:N127 I would like the macro to recognized that there is new data and update to P127 with the new calculation....
I was sure it would be in your archives but couldn't find it - apologies if this is a FAQ.
I am trying to extract the data in a spreadsheet to a bunch of files, one file per cell. The cells I need have very long text strings in them, actually whole HTML files.
Using this
Dim HTML As String HTML = Cells(RowNdx, ColNdx).text
I can only read 1024 bytes - not nearly enough. Is there something else I can do to read the cell into a string variable?
I want to use the Camera facility to show a 13 column by 19 row section of a large report in another WorkBook. I have outlined the area I need copied .... clicked the Camera icon .....but when I enter the data in the receiving sheet I keep getting the message ...." The Picture is too large and will be truncated" Is there a maximum size that can be transferred
I often end up in a scenario where I want to be able to move text from one column into another using a formula, which involves pasting a formula down a filtered range... E.g. Column A contains both ID numbers & dates, I want to move ID numbers into Column B, so I apply a filter to all ID numbers using a formula "=A1" and paste it down the filtered range.
It becomes an issue when there is greater than 50 - 60 thousand rows - excel throws an error "selection range is too complex" and disregards the filtered range.
Only thing I can think of would be a VBA script to automate pasting a formula in blocks of 50,000 rows.
I have a database that needs breaking down in order to fulfill a request.
I've been trying to create a macro to copy one row to a new workbook (starting from row 3), file name save as a value of the cell (C1), and move onto the next row. However, my code appears to only loop through 26 entries and then stops.
Excel 2007-2010. I'm using match(string, range,0) but there must be a limitation on the length of the string since I know the string is in the range but it returns #value as if it is not found. Is there a VBA solution to get around this without having to loop/cycle through the entire range?
method. It seems like the limitation is in the Countif function going over 1000 (or some other size limit)
I have a list of ~1500 rows, of that there are approximately 55 unique items. Doing the unique array works correctly up until item ~40, upon which it fails by returning the 1st item only (for the rest).
Trying to debug, and pulling out the Match section, it functions up until 976 (that is
MATCH(0,COUNTIF($N$1:$N40,$C$3:$C$1500),0)
returns 976) anything after returns just 1 (1st item).
It seems to be a limitation on the text string size that COUNTIF can handle.
Can a Function give two or more output variables. e.g.
Sub a() x = 5 result = Y(x) End Sub
Function Y (x As Integer) As Integer Dim B B = ... * x Y = ... * B
this will give back Y as a result. But if I want to get 2 or more output variables (let's say I need to get also B into sub) from one function, how should I do that? I need this because function works with large matrix and I want to extract some values appeared in between.
I'm trying to loop through a range in excel from access, checking where the titles (in Excel row 1) match with the fields (in a recordset in Access that is passed to the function) - and where they do, I want to dimension a variable to hold the column number - I'm not sure it's possible, but I'd be interested to know either way. The line I'm asking about is at the bottom of the code - the rest of the code is just to give context...
Sub ImportGeneric(rsImported As ADODB.Recordset, rsConfirmed As ADODB.Recordset) Dim fd As FileDialog Dim xl As New Excel.Application Dim wb As Excel.Workbook Dim ws As Worksheet Dim iFilePicked As Integer Dim strFilePath As String fd.Filters.clear fd.Filters.Add "Excel files", "*.xls" fd.ButtonName = "Select" iFilePicked = fd.Show If iFilePicked = -1 Then strFilePath = fd.SelectedItems(1) Else ..................
This formula is set in a "list" validation. So if I click drop down and select "1" I get a return of .05% obviously as the formula shows......ok, we got it!
Problem: So now I want to add IF E14=0,".0%, but it will exceed my precious "7" IF function max? Can someone PLEASE help the rookie with this formula.