I'm looking for a way to identify the caller procedure and to act according to that.
I tried the CALLER command but it seems not to be the appropriate one. The sample code is as follows:
Sub AAA()
MsgBox "This MsgBox is displayed from within Proc. AAA"
BBB
CCC
MsgBox "This should NOT to be shown if Proc CCC was called from here"
End Sub ............
I am trying to determine how to get the code below to fire whenever cell J10 is populated and do nothing when cell J10 is not populated but I can't quite get it. (Cell J10 is manually changed and is not changed based off of a formula)
View 4 Replies View RelatedI would like to be able to count the number of rows with data and use it in a macro. For instance, Columns A:C and Rows 1:50 or more have data (the number of rows are variable). In Cell D1 I have a simple formula
Code:
=A1&","&B1&","&C1
I am using a formula in VBA like:
Code:
Range("D2").Select
Selection.Copy
Range("D3:D10000").Select
ActiveSheet.Paste
I don't want to have an arbitrary number like D10000 in the range because it will paste in cells even if there is nothing there resulting in a ,, in the cell. I know this is avoidable by wrapping in a
Code:
=IF(ISBLANK(A1)=TRUE,"",D1)
but the "" is not truly blank and jacks up the sorting.
If there is a better way by setting the row count to a variable like
Code:
countx=COUNTIF(A:A)
and then using it in the selection such as:
Code:
Range("D2").Select
Selection.Copy
Range("D3:countx").Select
ActiveSheet.Paste
Am I going out about this wrong, maybe missing something fundamental about this?
I have been trying to determine the maximum/minimum value and additional count from a variable range which then I can use to subtract the first data of the range; I have been able to do that for a fixed range but not a variable one.
I have Column A with random positive numbers.
I have Column B with random negative numbers.
I have Column C with random numbers.
I have Column D with random numbers.
I have Column E with random 0’s and 1’s.
I have a set of 1600 cells of numerical data on each of the columns and there will be times when the random 0’s and 1’s from Column D will have appear repeatedly before changing to the opposite number, fx, I will get 7 nr. 1s before I get a 0 (zero).
I have been able to obtain the values in 2 cells within a fixed range:
If the last 8 cells in Column D have been “1” then I do:
F1=MAX(A1:A8)
G1=INDEX(C1:C8;MATCH(9.99999999999999E+307;C1:C8)) ---(Which provides the first number of the range in Column C after Column D has changed from 0 in D9 to 1 in D8)
H1=(F1-G1)
If the next 3 cells in Column D have been “0” then I do:
F9=MIN(B9:B11)
G9=INDEX(C9:C11;MATCH(9.99999999999999E+307;C9:C11)) ---(Which provides the first number of this range after Column D has changed from 1 in D12 to 0 in D11)
H9=(G1-F1)
Additional to this, the COUNT has also been challenging since I want to obtain in Column I, the COUNT of repeated 1’s from each range of 1’s in Column D; and also in Column J the same but for 0’s.
Fx:I18 (size of the range of 1’s originated from D1:D8)
J93 (size of the range of 0’s which consist to be the next range before 1’s were originated)
I12X (size of the range of 1’s which consist to be the next range before changed to 0’s)
However, that only works for a fixed range and continuous updates are made.
Book1.xlsx
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
I need to build an Excel application that allows a user to select and download files from a website. The files change daily. Is there a way in VBA to obtain the current list of files that are available for downloading?
View 4 Replies View RelatedI am trying to reference a cell that displays "#NAME?" due to an unrecognized formula. However, I keep getting an "Type Mismatch" error. Is there a way to reference a file that displays "#NAME?"
Example:
If Sheets("sheet1").Range("B6") = "#name?" Then
Sheets("sheet1").Range("B3").ClearContents
End If
This is probably a simple question, but I couldn't find the answer by searching...
I have a macro that I wish to run which calls one of two different macro's depending on if the user has Excel version 2007 or if they have an earlier version.
This is to overcome 'Save as PDF', which only works in Excel 2007.
When I click on a commandbutton20 on sheet1 it moves the user from sheet1 to sheet2, hides some columns & formats the height of a row in a range of cells I will refer to as Area X. On sheet 2 I have a target condition that if the user clicks on any part of Area X it move the cursor to cell C8. What I want to accomplish is that if commandbutton20 in clicked, the target condition is suspended. otherwise the target condition works. The code I wrote is as follows. What happens when I press F5 to execute it... a macro window pops up.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("a1:x3,e4:x6")) Is Nothing Then
If Worksheets(1).CommandButton20_Click() = True Then
Exit Sub
Else: Range("c8").Select
End If
End If
End Sub
Trying to manipulate a macro I recorded to be slightly more dynamic with some VBA code. The macro has saved me a lot of time however each month I have to re-do it due to the date changing and I'm sure there must be an easy way to adapt the below code.
Current Code (it repeats afterwards as there are lots and lots of sheets I need to perform the action on).
Windows("Raw Data.xls").Activate
ActiveWindow.DisplayWorkbookTabs = True
Sheets("Calls").Select
Range("C43").Select
Selection.Copy
Windows("Volume Tracking.xlsm").Activate
Sheets("Offline").Select
Range("AE33").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
What I'm after is a way to alter the range highlighted based upon matching a cell in "Raw Data.xls' and a cell in "Volume Tracking.xlsm".
The cell A43 in "Raw Data.xls" contains a month (which changes every month); if the month is Jan then I would want the Range to remain as AE33. If it's Feb then I would want the Range to be AF33 (and so on).
In "Volume Tracking.xlsm" I have the months in cells AE4 (Jan), AE5 (Feb) etc.
Is there a way to locate the column in "Volume Tracking.xlsm" on row 4 that contains the value found in A43 in "Raw Data.xls" and then paste what was copied out of "Raw Data.xls" C43 and paste it in to row 33 of the correct column that holds the matching month?
If not could I add code to say if cell A43 in "Raw Data.xls" = Jan then paste to AE33, if Feb then paste to AF33 and so on.........
Excel 2007
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.
0
284.6554
419.3615
565.5539
[Code].....
The row on the top currently matches the column on the left side. This was done by using simple commands like "=A4", "=A5", "=A6" in the top row. Now I am trying to expand this table to a variable amount of columns and rows (there will always be 3 more rows than columns like there currently is above), so I want to write some code in VBA that will put these equations in the cells for me.
Here is the tricky part though. The numbers in the left column are actually calculated using the solver, and the numbers in the middle cells reference both the left column and top row. Because of this, I actually need to put an equation in the top row (like the previously mentioned "=A4") so that it always has the newest result from the solver.
The left column will always be in the same row, so only the number itself needs to change, but I'm not sure how to do this.
I have been using this code to auto print a series of workbooks whose file date contains the day i want. Each if statement looks to a different folder location to find the spreadsheet with the desired date and auto prints that sheet before moving on to the next if and spreadsheet, and then finally closing. I use an input box to get the date/day variable.
The spreadsheets i auto print are in folders labeled for different months of the year, i.e. 0107, 0207 etc. I want to be able to enter the MONTH i want the spath to look for, in other words, i want to be able to input first the month, which tells the rest of the routine what folder to look for the next input, which is the date of the spreadsheet. I am flummoxed by the variable itself.
The code is below.
Public Sub Auto_Open()
Dim sCurFile As String
Dim sPath As String
fpath = InputBox("shift and day (BXX) of the month to print?", "print")
'Get the path
sPath = "k:2007207"
If sPath <> "" Then
On Error Resume Next
Application. ScreenUpdating = False
If Right(sPath, 1) <> "" Then
sPath = sPath & ""
End If
I have a spreadsheet that has several buttons on that run code using the cells around the code for parts of the data. eg
ColumnA1 ColumnB1 ColumnC1 Button1
ColumnA2 ColumnB2 ColumnC2 Button2
I want the buttons to run a seperate piece of code to start, but then all buttons run a similar piece of code at the end. I am hoping to be able to pass a variable from the first piece of code each button runs into the section of code that all share (which I have done as a seperate macro)
I have a spreadsheet with about 30 charts on it that I would like to attach a macro to which opens the chart in a form. I've figured out how to do this last part, but am stuck on how to pass a variable to the form code which tells excel which chart to copy. Essentially what I'm doing creating a macro for each chart which would run when that chart is clicked on. This macro is identical for each chart except for the name of the chart being passed.
Sub Chart1_click()
ShowChart "Chart 1"
End Sub
Sub ShowChart(c As String)
frmChart.Show
End Sub
In the UserForm_Initialize code located in the userform module, I'd like to call the code which saves and loads the image of the clicked on chart. But here's where I'm stuck. How do I pass c to the code in the form module...e.g, how to pass c to UserForm_Intialize and to ChartZoom? Here's what I have so far...
Private Sub UserForm_Initialize(c As String)
ChartZoom c
End Sub
Private Sub ChartZoom(c As String)
frmChart.Show
Dim Cht As Chart
Set Cht = ActiveSheet.ChartObjects(c).chart
Dim CName As String
CName = ThisWorkbook.Path & "cht.gif"
Cht.Export Filename:=CName, FilterName:="GIF"
imgCht.PictureSizeMode = fmPictureSizeModeZoom
imgCht.Picture = LoadPicture(CName)
End Sub
I am trying to figure out how to use a variable as a row number for use in a range name and/or a pivot table range. Right now I have a range of R571C17, but the row number will change with each use of the pivot table formation macro.
View 3 Replies View RelatedJust a niggling problem, I've got lstRow as a Long and it contains the value of the last row offset by (1, 0). The problem is i'm trying to add it into a range
Range("C500:K500").Select
Selection.autofill Destination:=Range(C" & lstRow &":K" & lstRow &")Type:=xlFillDefault
Like that.. Except i've tried a million different combinations of " and & in different places to try and get it to compile. It refuses too. It always gives the error "Expected list seperator or )" I know I could just do it individually from C to K but I'd like to learn how to do it this way as well.
I would like a function which works like the NPV function except that the interest rate should be variable and a range rather than a single number.
Is it possible to see the code for the excel NPV function so that I can modify it?
Or alternatively has seen such a function?
I have piece of code that I'm trying to make dynamic. I want to store parts of an IF statement in a variable and use them when I need them. Here's a little sample of what I'm trying to do
View 9 Replies View RelatedI need a code which will open up a previous days file. I have the code to pick the correct file but I am stuck because the folders are multiple levels down to year then month.
Eg. Folder Name > YYYY > Month
So i need to have a code which can look in to the relevant folder based on the current date and then be able to open the file i need.
I.e. today is 29May so i need the code to open the 2014>May folder.
For 1st day of month I would need it to open last months folder I.e. 01June Open May folder
I suppose this is kind of SQL as much as anything so apologies if this isn't really the right place for it.
I had an idea of how to use a concatenated string in Excel to utilise the SQL IN statement for listing parameters that vary from day to day.
Annoyingly my code does not work:
If my range of codes is for example
XS0330509760
XS0242821717
XS0241172948.................
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 have a set of worksheets (Sheet31 through Sheet49 by codename) produced by a machine in the lab. I want to create a summary sheet that references cells in those worksheets.
Rather than go through the annoyance of doing this manually 40+ times I'm working on creating a macro to populate the summary sheet for me.
This is what I have so far
For J = 31 To 49
ResultSht = SheetJ.Name
Sheet1.Activate
ActiveSheet.Range("A4").End(xlDown).Offset(1, 0).Select
It hangs up on the "SheetJ.Name" however. My objective is to be using the object SheetJ, where the object SheetJ is the codename of the results sheet I'm trying to reference (for example, in the first pass through the loop it would be Sheet31). Could someone point out my mistake in this object reference?
I am needing to write a line of VB code for a macro that will insert a VLookup formula into a cell where the "named" table_array can be a variable. Example of what I am looking at below.
Worksheets("active Worksheet").("active cell").Formula =VLOOKUP(G2,variable,6,False)"
I need it to be imputed in the active cell of the active sheet with the variable able to be gathered possibly from a cell reference. Say the cell c3 on the active sheet says V080606, the formula imputed would be =VLOOKUP(g2,v0806,6,false).
I have a VBA form that the user enters information into and once they click submit on the form the code opens a new workbook and then enters the information from the form into that workbook. What I need is for that code to then save the workbook with a filename from one of the fields entered into the form but I'm not too sure how to do this.
View 3 Replies View RelatedI recently wrote the following macro to drive several dimensions in a Solidworks assembly using cell values in excel. The strange thing is that I witnessed the macro work on several occassions with the exact same code, however now it always produces "Run-time error 91: Object variable or With block variable not set". I read in another forum that macros in excel can become fragmented with repeated editing, and unloading all the macros then loading everything back up again actually worked for a little while, however I would rather fix an issue with the code if there is one:
Note: In Debug mode the lines
Part.Parameter("D1@Sketch1@CORE INNER.Part").SystemValue = _
Sheets("AEM Core and No Load Loss").Range("B4").Value / 1000
are highlighted
Sub Generate_Core()
Set swApp = CreateObject("SldWorks.Application")
Set Part = swApp.ActiveDoc
Set SelMgr = Part.SelectionManager
Part.Parameter("D1@Sketch1@CORE INNER.Part").SystemValue = _
Sheets("AEM Core and No Load Loss").Range("B4").Value / 1000
Part.Parameter("D2@Sketch1@CORE INNER.Part").SystemValue = _
Sheets("AEM Core and No Load Loss").Range("B5").Value / 1000................
I have a front sheet with a list of all the sheets in the workbook in column A.
In column B I need to use the counta function to count the number of entries in the corresponding sheet to column A.
As there are 70 sheets I'd thought I'd use a Macro but I'm having great difficulties. I've done a search but can't find what I'm looking for.
So heres part of my code (it uses loops etc which I've managed but this just errors)
cell.Offset(0, 1).FormulaR1C1 = "=counta(" & str2&" C[-1])-1"
The str2 is the string name to reference the sheet in the formula (for example sheet1), I can't figure out how to enter this into the code so it works and is accepted.
I have the following code to execute a connection to a DB and return the result of the SQL statement. I have a variable in the statement that I want to be able to change from user input in a single cell. Unfortunately I am unable to get this to work.
Here is my code
cell_value = Sheets("some sheet").Range("P8")
Application.ScreenUpdating = False
Sheets("some sheet").Cells.Clear
Set rs = New ADODB.Recordset
[Code] ........
This fails and gives me a debug error, however if I declare the condition I want the code executes fine. This would mean having to update the code every time the condition changed or putting the whole query in a cell on the sheet which I do not want to do.
I am working with a large report that needs to be broken out and sent as separate files to recipients for confidentiality purposes. I'd rather not use views/protection since there are many different ways particular people need to see the data, plus it is a very large file and flattening it works to everyone's advantage. My goal is a macro that will copy each tab into separate workbooks, paste special values, and save as each as Cell A1 (or the tab name-same thing). I have tried recording macros and editing (I'm very new to VBA) many times but it's a mess.
View 2 Replies View RelatedI am having to copy and paste rows of data into a new worksheet where the rows sizes change and I am wanting to add a new row at the end of the pasted rows but with the sumation formula to add the relevant column
e.g copy range B14:AA17 with in this case columns E to AA holding the numerical values. Therefore I wish in cell E18 to sum the value of E14:E17 and so on ending with cell AA18 holding the sum of AA14:AA17
As these vary I have all relevant variables, Range to add sumation values to eg E18:AA18
Start Cell E14 and so on.
I tried adding "=SUM(x:d)" where x and d are vars relating the the column cell required eg x = E14 and d = E17