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 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.
In a Worksheet named "Input" and in Cells "B3:G?" I have 6 number groups. How can I ...
( 1 ) Put each of the 6 number groups into an array when I don't know what the last row number is.
( 2 ) Find the maximum value in any of those cells and attach it to a variable named MaxVal.
... so once I have done this I can then loop through each 6 number group and perform some calculations.
I do hav a sheet in which i do hav some column;s n data
Worktype
Metrics
April
May
June
in work type few rows are hidden which i don't need what i need is i want to populate only comman metrics in other range named metrics i had build a macro for this and that's working fine but the problem is it also reads metrics from hidden rows too. i had tried a loop in which i had placed as hidden column property condition.
I'm trying to open a file on a network drive...but I'm getting the following error message when it opens: "This file may be read-only, or you may be trying to access a read-only location. Or the server the document is stored on may not be responding." Now, the file itself has no rights restrictions and is not read only. It doesn't appear to be locked.
Now, there are other Excel files in the same directory which I could open fine; however, the Excel documents having the above problem all have a little black icon "appears to be a padlock" (image attached) at the bottom left hand side of the Excel file icon. I tried the following:
- Renaming
- Converting to a different file format (didn't work, it won't let me)
- Opening in notepad...etc doesn't work.
This file is dated back in 2004...do you think it's corrupt? Is there anything i can do to open or recover this?
I was wondering if there is a way to set a macro to run on an event only if the workbook is read-only. The macro I have set up right now reformats the toolbars to a custom user friendly one, but I want to be able to open the document with a password and not have the toolbars rearange on me.
View 2 Replies View RelatedI have been using various bits of information from all sorts of threads and made a somewhat nifty little Macro program for the company i work for.
The following threads may have my answer in them, but I can't see it or figure out how it could be restructured to my needs.
Disable Read-only Message Box
looks as if the file is set as readonly then to close it without saving
[url]
[url]
contains a msgbox I would like to add in, specifying that the file is read only therefore to abort current macro for particular file
concerns very much the problem I am having.
Basically what it boils down to is,
My Program Manager runs a daily report, then uses my program to update up to 7 different workbooks. Each having their own Sub program.
Some of the Supervisors or leads gets in to early and thus makes the file as a Read-Only, which of course stops my program and can not continue.
What I would like to learn is.. (from the countless threads I have looked through, I can't seem to find the answer)
Bring up a msgbox, informing the PM that a specified file is already open and therefore this particular process has been aborted.
Then it will move on to the next Sub, and so forth...
I want to thank everyone for their help directly or indirectly, (been reading threads for about 9 months now) and any future help as well.
And if it's not to difficult, could it be close to layman terms? I try to understand alot of these codes and I get lost with some of the wording. I am just pratically piecing pieces together with my "eyes closed" and somehow come out with a working VB codes.
code to pass a range say (A1:A3) as an arguement among other arguements to a custom function and then reading this as an array inside the custom funtion
View 4 Replies View RelatedI have come across another stumbling block with VBA (new on VBA and slowly taking baby steps). I have a macro that reads and deletes all duplicates on the files i work on. However I need this to read and do the job on 2 sheets at the sametime (sheet 2 and sheet 3). Below is the macro which does work fine on a different workbook. How would I integrate this into my new sheet to do 2 jobs at once?
VB:
Sub delduprows()
Dim m As Integer, j As Integer
Dim n As Long, i As Long
[Code]....
I want to design a macro to check if the cell has a comment in it. If so, the macro should read the comment and store it in a variable.
View 2 Replies View Relatedbelow code
Sub Collect_OS_Data()
With Application
.ScreenUpdating = False
.EnableEvents = False
End With
Dim fso As Object, fld As Object, fil As Object, fldPath As String, wbSrc As Workbook, wbCur As Workbook
fldPath = "Q:PublicPAYMENTS Q&RREPORTSSuspense Activity BUSINESSOngoing - Suspense BUSINESS - Activity Reports"
Set wbCur = ActiveWorkbook
ActiveSheet.Name = "Raw Data"
Set fso = CreateObject("Scripting.FileSystemObject")
Set fld = fso.getfolder(fldPath)
On Error GoTo ErrHandler
For Each fil In fld.Files
This opens all spreadsheets within a folder and takes the data i require out of each one and pastes to a new workbook, however the files are password protected. Can you add into the code to open each spreadsheet as a readonly copy and paste data and then close without a save prompt or denying the save prompt ?
I have the following code which reads line 54 (row 54) in a spreadsheet and calculates the following using data in subsequent columns. Sometimes the data I need isn't always in row 54, I would rather have the macro read the data in Column A[AB] that is on line 54...sometimes on 53.
The date is Sep-09. How can I make it read this rather than AB54. I think I just need changes @ the top (ie Dim rng as Range, Set rng = [AB54]).
Sub Profile_info2()
Dim rng As Range
Set rng = [AB54]
rng.FormulaR1C1 = "=RC[-12]+R[-1]C[-12]+R[-2]C[-12]"
With rng.Offset(0, 2)
.FormulaR1C1 = "=((RC[-28]+R[-1]C[-28]+R[-2]C[-28])*4)/((RC[-25]+R[-1]C[-25]+R[-2]C[-25])/3)"
.NumberFormat = "0.0"
End With
With rng.Offset(0, 3)
.FormulaR1C1 = _
"=((RC[-15]-RC[-29])+(R[-1]C[-15]-R[-1]C[-29])+(R[-2]C[-15]-R[-2]C[-29]))/(RC[-15]+R[-1]C[-15]+R[-2]C[-15])"
.Style = "Percent"
.NumberFormat = "0.0%"
End With
rng.Offset(0, 4).FormulaR1C1 = "=RC[-8]"
With rng.Offset(0, 5)
.FormulaR1C1 = "=(((RC[-17]-RC[-31])+(R[-1]C[-17]-R[-1]C[-31])+(R[-2]C[-17]-R[-2]C[-31]))*4)/((RC[-28]+R[-1]C[-28]+R[-2]C[-28])/3)"
.Style = "Percent"
End With
End Sub
I need to use the "IF" formula on a worksheet with 15 variables. There is a limit of 7 that can be used at once, does anyone know a way around this?
View 6 Replies View RelatedDoes Excel can be used to read data from HDF files? Specifically, what I would like to do is this: I have an Excel worksheet with some latitude data on Column A and longitude data on Column B. I would like to open a HDF file, which contains many sets of data, out of which 2 are the latitude/longitude data, and a third set contains the data that I want to extract. I will use the latitude data in Column A to search through the first set in the HDF, which will return me the row number. I will then use the longitude data in Column B to search through the second set to return the column number. With these row and column numbers, I will then extract the corresponding data in the third set, and write it to Column C in the worksheet.
how to write a simple VBA code for it? Or point me to some relevant information?
I have a pair of macros that together close and reopen a workbook when the user clicks on a button labelled 'Refresh'. The reason for the macros is that any one of multiple users may have the workbook open for editing while the others have it open read-only, and those who have it open read-only will want to refresh it periodically to make sure they're viewing the most up-to-date version.
Now, here's the hitch. If I have the workbook open read-only and refresh it while no-one else has it open for editing, it doesn't reopen read-only (it reopens for editing). But I don't want it to reopen for editing (I want it to reopen read-only). Is there anything I can add to the macros to make the workbook reopen read-only, or is there a different pair of macros I can use? Here's what I have at the moment:
Macro 1
Sub CloseMe()
'
Application.OnTime Now, "OpenMe"
ThisWorkbook.Close SaveChanges:=False
'
End Sub
Macro 2
Sub OpenMe()
'
End Sub
I've written a script to read all the values in an excel worksheet in to an array. The cell types differ from General or Text to Custom - HH:MM When I look through the array the script has returned the correct value for most but for the cells that are of a custom type an "incorrect" value has been returned. For example a cell of type Custom HH:MM which is 10:23:00 in the worksheet appears as 0.432638888888889 in the array. Is there a way to get the script to return the correct value?
Below is an extract of the code I've used
If objWorksheet.Cells(i, "D") <> "" Then
If objWorksheet.Cells(i, "F") <> "" Then
arrCULDEV = Split(objWorksheet.Cells(i, "F"), ":")
Redim Preserve arrSheetInfo(x)
arrSheetInfo(x) = arrCULDEV(0) & "," & arrCULDEV(1)
x = x + 1
i = i + 1
Else
i = i + 1
End If
End If
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...
=IF(A164="A",ABankList,IF(A164="B",BBankList,IF(A164="C",CBankList,IF(A164="D",DBankList,IF(A164="E" ,EBankList,IF(A164="F",FBankList,IF(A164="G",GBankList,IF(A164="H",HBankList))))))))
Here it is:
I used ceiling and floor for this but it was futile.
I was hoping to round off a no. according to the limitations set,
For example:
Sample Entries Preferred Rounded-off nos.
Cell A1 Cell B1
.40 .40
.39 .40
.38 .40
.37 .35
.36 .35
.35 .35
.34 .35
.33 .35
.32 .30
.31 .30
.30 .30
When using ceiling and floor formulas there was an error prompt telling me that there are too many arguments.
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 have been using this formula below:
=IF(B5=1,NNO!C8,IF(B5=2,NSO!C8,IF(B5=3,NWE!C8,IF(B5=4,NCN!C8,IF(B5=5,NCS!C8,IF(B5=6,NHO!C8))))))
It works for one cell at a time- but only allows 7 variables at a time and I have 30.
Which means I would need 4 or 5 drop down boxes in stead of one.
Please see the attached GIF.
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 macro that copies time cells from another sheet and puts them in cells in another sheet, I also have another macro that takes 30 mins away from that time if a certain condition is reached but it wont work unless i retype over the copied cell.
View 14 Replies View RelatedI'm working with a workbook that has about 75 sheets. I need a macro that will copy the info in a certain couple of ranges from one sheet to the same ranges in another. This would not be a problem if it was always the same two sheets. But I need the user to be able to specify which sheet he wants to copy from and which sheet he wants to copy to.
The ranges on the sheets will always remain the same, just the sheet name needs to change. I created drop down boxes for the user to choose the copy from and copy to sheets, but I don't know how to insert that into the VBA code. The way I've designed it, the macro needs to read the result from the user that is on sheet "Index" in cell H5 to copy from and N5 to copy to. The result in those cells is the actual sheet name.
Here's the simple part of the code so far:
Sheets("MM Ades 64z").Select
Range("B8:B59").Select
Selection.Copy
Sheets("MM Pnch 64z").Select
Range("B8:B59").Select
ActiveSheet.Paste
Sheets("MM Ades 64z").Select
Range("D8:D59").Select
Selection.Copy
Sheets("MM Pnch 64z").Select
Range("D8:D59").Select
ActiveSheet.Paste
Sheets("MM Ades 64z").Select
Range("J8:O59").Select
Selection.Copy
Sheets("MM Pnch 64z").Select
Range("J8:O59").Select
ActiveSheet.Paste
Sheets("TMMC").Select
Range("M1").Select
End Sub
i have couple of textbox in my userform and i want to limit them for 7 number and one letter e.g. 7777777X.
View 2 Replies View RelatedI 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.
=IF(I3="Jan",IF(ISNUMBER(SEARCH("NA",Data!$B$355)),"NA",Data!$B$355),
IF(I3="Feb",IF(ISNUMBER(SEARCH("NA",Data!$C$355)),"NA",Data!$C$355),
IF(I3="Mar",IF(ISNUMBER(SEARCH("NA",Data!$D$355)),"NA",Data!$D$355),
[Code] ............
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.
ABC
1Range1Range2
2red0%
3amber0%5%
4green5%10%
5purple10%
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 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 ?
Sheets("main").Select
ActiveSheet.Shapes("Rectangle 1").Select
messagebody = Selection.Characters.Text
....
email.Body = messagebody
I have three workbooks that contain various types of information. I have an Overtime workbook that lists employees and calculates the straight time and overtime worked for the day. This is the main log that information will be pulled from.
I need writing a macro that will copy over information to an Absent Log workbook and a Production Model workbook.
The Absent Log workbook:
This workbook contains worksheets from each month. I need excel to do a lookup of the name in the Overtime workbook and copy the information from the "OT" column but if that column is empty I need it to pull the information from the "Personal DT" column.
The Production Model workbook:
I need this workbook to automatically pull the total straight hours and overtime hours to the production model for the correct day. This will also pull from multiple sheets.
I am going to be writing this in Excel 2010 but it will be primarily used in Excel 2003.
I uploaded the workbooks to filefactory.
OT Workbook: [URL] ......
Absent "Log" workbook: [URL] .....
Production Model: [URL] .....