I am trying to create a primitive counter. My idea is to write a value to a file, read it, add one and write it back. The file as a single value tab-delimted of 5000. The code below produces the odd output on debug. I really cant understand it.
Public Function nextInv() As Integer
Dim fname As String
Dim fnum As Integer
Dim stringArray()
Dim invNum As Integer
fnum = FreeFile()
fname = "nextInv.txt"
stringArray() = parse(OpenRecordClassesFn(fname), vbTab)
For i = UBound(stringArray) + 1 To LBound(stringArray)
invNum = stringArray(i)
Next
If FileExists(fname) Then
Open fname For Output As #fnum
End If
invNum = invNum + 1
Debug.Print "invNum"
Print #fnum, (invNum & vbTab)
Close #fnum
invNum = invNum + 1
End Function
Public Sub testHarness()
Debug.Print nextInv()
End Sub
with a output of
5000
invNum
0
and the value in the file of 1. It should be 5001.
Just starting with excell and am working on a project for my class. I cannot figure out why the value of the variable "changer" defined by command buttons on sheet1 are not being passed to the procedure contained in command button on sheet 2.
I declared the variable in the general declarations section of sheet1 with
Public changer As Integer
The command buttons on sheet 1 contain
Private Sub CommandButton1_Click() changer = 1 Cells(1, 1) = changer End Sub
Private Sub CommandButton2_Click() changer = 2 Cells(1, 1) = changer End Sub
The command button on sheet2 contains
Private Sub CommandButton1_Click() Cells(1, 1) = changer End Sub
When I click on the command button on sheet2 nothing happens.
Private Sub Workbook_Open() fileName = ThisWorkbook. Name fileLocation = ThisWorkbook.Path Dim strFound As Boolean, pos As Long If Not fileName = "" Then strFound = False pos = 1 Do While strFound = False stringFound = Mid(fileName, pos, 1) If stringFound = "-" Then productName = Mid(fileName, 1, pos - 1) MsgBox "Name of Product Is: " & productName strFound = True Else pos = pos + 1 End If Loop End If End Sub
It would return "Test" if the file's name is "Test-Part1.xls". I then declare the productName variable as Public (Public productName As String) in one of the modules. But the productName only holds the value for a certain amount of time. After a while, it's empty.
about variable lifetime/scope in a workbook. Basically I have a Class Module that I set up in the Workbook_Open() event as follows:
Private Sub Workbook_Open()
Set CurrentClient = New CClient
End Sub
Once the workbook is open the user can select their "client" and all properties/methods for that "client" are available throughout the workbook until they close it, and it all works fine. However, whilst developing I'm obviously making lots of changes to the code, and every time I do the object is no longer available in memory.
I've tried manually running the Workbook_Open event after I make a code change, and even put in a button with the same code, but whatever I do the object won't remain in memory until I close & reopen the workbook. It's so frustrating having to close & reopen the whole workbook every time I want to test a code change I've made!
I used the macro recorder to take selected cells from a worksheet and automaically print the selected area. It works great - but i (as usual) want it do more - more specifically, i want it to print, say 10 rows, from any row i select.
The worksheet is a live schedule which is updated constantly as production works through the scheduled orders. I want to identify the row which has the current running production order and print that row and the next ten.
how to use the input box to ask for the row # but don't know how to code the actual passing of that variable. I figure i can then marry my existing code to make a working frankensteinish macro.
I have a multitude of reports to work on. Right now, the headers are made manually using the first few rows of each sheet, which is very inefficient, and difficult to get them to look the same. If the sheet is put on "Fit to X pages", the header is shrunk still. The reason this is done is because in the header / footer section of Excel 2002/2003, you can only use static text and predefined footer data (pg 1 of X, date, etc). Basically, I'd like to use a header that stays the same size, format, look between sheets/workbooks, but can accept variables in some way.
I export data from another programme into excel and have written a macro to move and format this. The number of rows of data exported will be different each time. I need to be able to print this data and want to add it to the macro. I have learnt to use this code to select this data working from the last used cell to the top
Using the macro recorder for printing one of my exports I got this code
Range("A1:D53").Select Range("D53").Activate With ActiveSheet.PageSetup .PrintTitleRows = "" .PrintTitleColumns = "" End With ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup.......................
I am trying to write VBA code that will print a print range that is presented in cell F3 on a "Reports" worksheet. The content of F3 will change depending on how many reports the user selects to print. For example, he could select one, two, three reports etc - up to twelve. The cell ranges of each report are named (e.g. Report1, Report2 etc) so that if the user selects to print Reports 1 and 2, the contents of cell F3 are "Report1,Report2". If I replace WhatToPrint with "Report1,Report2" the print macro works.
Sub Macro2() Dim WhatToPrint As String WhatToPrint = Sheets("Reports").Cells(3, 6).Value 'sets the variable to equal the contents of cell D3 which contains the formula 'summarising the print ranges I want to print Sheets("Reports").Cells(3, 6).Select ActiveCell.FormulaR1C1 = WhatToPrint ' pastes the variable in cell F3 - just to check that it looks like I want it to Sheets("Reports").PageSetup.PrintArea = WhatToPrint 'uses the variable to set print area - this is where it fails! 'if you replace the variable with the contents of cell F3 the macro will work ActiveWindow.SelectedSheets.PrintPreview End Sub
I would like to know if there is a way to print several different workbooks at once but keeping my printing format which I would like to be Landscape and Fit to one page. Reason is simple as I work in a office where staff is handed in several jobs to do everyday. They finish the jobs and log all the info on the database. I log on to database and put all their daily diaries and because it is all over the place I have to go in each file and set printing preferences which takes an hour in the morning and hour in the evening. I could do with some sort of automation where all diaries are automatically printed in Landscape and Fit on one page.
I have a report that is generated from a manufacturing process that looks like the example below. the report is 40 pages long when all the data is printed. i am looking for a way to only print this range if a dimension is "out of tolerance". if the dimension is within tolerance, there is always the "garbage" text of plus and minus. if every row is "within tolerance" in the range the cells in the OutTol column would all contain the "garbage" text but it will not always be identical. so, in summary, actual OutTol values = print and all "garbage" = not printed.
How to prevent a variable holding a pointer to an object from going out of scope and releasing the object pointer after the VB project is reset due to a runtime error during code execution or due to manually resetting the vb project ?
Hooking the Application events in a class module is a scenario where this could be particularly useful - For example, let's assume the application events are hooked upon opening the workbook . Now if the variables go out of scope the application events are no longer functioning which is what i am trying to avoid.
I did once worked around this by creating a VBS script on the fly and continually monitoring the state of the variable from outside excel but this approach has proven problematic on some machines where running scripts is restricted.
I thought about writing an activeX dll in VB6 , loading it in the excel process and hook the application events from there but I don't think this would work because the dll shares the same memory space as the host process.
I'm trying to find definitions/commands to make excel find the best combinations of several defined scopes and restrictions to make an output number as high as possible.
This is by using a combination of several different equations, maybe excel has a magic trick for all the variables and formulas.
It's some code I found and altered that automatically updates the cell to the right of a column that has drop down box data validation, creating a list of the selected values seperated by commas. The thing is, I only want it to do this in columns 8 and 10 but data validation drop-down boxes have been used in several other columns. I've tried various methods of limiting the scope, the latest being the line: If Target.Column = 8 Or 10 Then. But the auto update is still being applied to all drop down boxes in the sheet. So, how can I make sure the auto update only happens when the drop down boxes in columns 8 and 10, updating into columns 9 and 11.
I have two numbers I need to compare using and IF statement. The two numbers are virtually the same, and I want the IF statement to round the numbers so any difference under 100,000 is not detected. For example:
Cell A1 = $32,194,884.45 Cell B1 = $32,194,884.37
I would like the statement to look at this number as 32.19 (divided by 1M). I am using the below equation currently, but it is not working:
I have a spreadsheet and I’m trying to create several different staffing scenarios Basically what I want is to populate column E with the amount of staffing based on the number of classes in column B. The range that I want to apply is in the green box in the sample. I’m trying to have excel give me the staffing value if the current number in cell B5 is a certain value – so in the example 1 through 4 = .2, 5 through 8 = .4 and 9 through 12 = .6. this site gave me some ideas so I tried V lookup and Index but I seem to be missing something and wonder if it is not combinations of nested functions?
I have something like this with data in around 1000rows there is text in collums A B D and E.
A B C D E 1 2 100 3 60 4 30 5 20
and i would like to do this: if value in cell x in collum C is over 80 then background color in Ax,Bx,Cx,Dx,Ex is yellow if value in cell x in collum C is between 60-80 then background color in Ax,Bx,Cx,Dx,Ex is brown if value in cell x in collum C is between 40-60 then background color in Ax,Bx,Cx,Dx,Ex is blue if value in cell x in collum C is between 0-40 then background color in Ax,Bx,Cx,Dx,Ex is red
Obviously i cant use conditional formating because i have more then 3 conditions.
I have a worksheet with 3 particular columns (L,M,N) that have drop down menus. The menu is populated with items that are referenced on a different workbook (different file). Because there are multiple worksheets accessing the referenced list, this is a master. When I change the master, the lists update, but the cell text does not. Example:
If I chose apple from the list, but later in the master I decide I want apple to be banana, while the drop dowm box will now have banana instead of apple, my cell still says apple.
I don't necessarily want apple to change to banana, but I want a visual cue that this is incorrect. I wanted the cell text to change to red if it is not equal to any of the list items on the master file. I want this to be passive, i.e., I want it to change automatically, so I don't have to press a button or some other activation.
I have the following code which performs the same action on 2 different tabs within the same workbook:
Dim Rng As Range, Dn As Range Set Rng = Range(Range("O1"), Range("O" & Rows.Count).End(xlUp)) Rng.NumberFormat = "@" For Each Dn In Rng If Len(Dn) < 7 Then Dn = String(7 - Len(Dn), "0") & Dn Next Dn
i get the following error:
Compile Error: Duplicate declaration in current scope
I am trying to automate the creation of 3 pivot tables. At first I was having a problem with deselecting all items in the drop-down except one, then I found the code to fix it.
This fixed the problem, but there are still two other pivot tables that need to be created after this first one. I scrolled down through the code and deleted the other code blocks for the selection of the PivotItems and replaced it with the code above, but then when I ran it I got the "Compile Error: Duplicate Declaration in current scope".
I read more through the forum and realize that it's because VBA is dimensioning it twice, and I read that you're supposed to Dimension at the beginning of your program, but how do I implement this into my coding?
I can't dimension literally at the start of the code, there is some formatting that needs to take place first. (Basically I paste in a bunch of data into Sheet1 of my workbook, center it, space it out, then insert a pivot table into the pre-existing Sheet2 based on that data, filtered on the blank entries in Column N. Then I need to create ANOTHER pivot table based on that same data in Sheet1, but filtered on the blank entries in Column O. Then I center the words in the Pivot tables' headers in Sheet2, and finally I need to create one last pivot table on pre-existing Sheet3 based on the data on Sheet1, filtered for 2 specific entry types in Column N.)
I have the following code to compare two columns and delete adjacent rows if 1 is greater than or equal the other...
Sub LastReceipt_GT_Confirmed()
Dim intLstRow As Integer
For intLstRow = Range("E" & Rows.Count).End(xlUp).Row To 2 Step -1 With Range("E" & intLstRow) If .Value > .Offset(0, 1).Value Then .EntireRow.Delete End With Next intLstRow For intLstRow = Range("E" & Rows.Count).End(xlUp).Row To 2 Step -1 .............
I am comparing a set of common variables across 2 sets of independent financial data. When comparing a variable across the 2 sets e.g. profit I want to colour a neighbouring cell based on the size of the difference. For example if the difference between the two values is within plus or minus 1 make it green, plus or minus 5 red etc.
I'm having a problem with data validation. I set an entire column so that it could only be a date between the first date in the list and the current date. I tested a few cells to make sure that it was working the way I wanted and I noticed that it allows a random number like 3 or 5 to be input after row 50. This would create a date in the 1900s. Why is this happening and how can I stop it.
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
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.
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.
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).
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.
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
