Free Microsoft Excel 2013 Quick Reference

Macro Set Variable to Function Value

I have a macro that counts the number of rows in a filtered worksheet. I need to save the value to use later. Currently the only way I know how to do it is to set a cell to the function value and then set the variable to the cell value.
eg.

	VB:
	
Range("D1").Select 
Selection.NumberFormat = "0" 
ActiveCell.Formula = "=SUBTOTAL(3,'[" & ReportName & "]Open Roles Sourcing'!A1:A999)-5" 
TotRows = ActiveCell.Value 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Why can't I just set the variable = function?


Post your answer or comment

comments powered by Disqus
this line:

	VB:
	

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
is causing me a run time error 424 when I try and run my code "Object required". Entire code is pasted below...


	VB:
	
 
 
Sub Macro1() 
     '
     '
     
     '
    Dim aescolumn 'create variable for index value of aes column
    Dim WorkbookName1 'variable for name of active workbook
    Dim pncolumn 'create variable for index of part number column
    Dim currow 'create variable for current row index
    Dim lastrow 'variable for value of last used row dependent on column A
    Dim crrow 'variable for active row
    Dim searchterm 'create variable for search term
    Dim stfound 'variable to determine if searchterm is found or not
    Dim fndcolumn 'create variable for index of part number location
    Dim fndrow 'create variable for index of part number location
    Dim aespn 'variable for the aes part number
     
     
    ActiveSheet.Cells.Find(What:="AES", After:=Cells(1, 1), MatchCase:=False).Activate 
     'find the aes part number column
    aescolumn = ActiveCell.Column 'set variable to index number
     
    WorkbookName1 = ActiveWorkbook.Name 
     
    ActiveSheet.Cells.Find(What:="Part Number").Activate 'find part number column
    pncolumn = ActiveCell.Column 'set variable to index number
     
    currow = ActiveCell.Row + 1 'make one low rower than current row (first value)
     
    lastrow = Application.CountA(ActiveSheet.Range("A:A")) 
     
     '=============================================
     
    For crrow = currow To lastrow 
        searchterm = Cells(crrow, pncolumn).Value 'set to value of first pn
         
        Windows("myworksheet.xls").Activate 'activate masterlist
         
        stfound = Cells.Find(What:=searchterm, After:=Cells(1, 1), MatchCase:=False).Activate 'find pn that is stored in
searchterm
        If Not stfound Is Nothing Then 'if value is found then do this
            fndcolumn = ActiveCell.Column 'set to index location of found value
            fndrow = ActiveCell.Row 'set to index location of found value
             
            aespn = Cells(fndrow, 3).Value 'set variable to the value of the aes part number
             
            Windows(WorkbookName1).Activate 'activate original workbook
             
            Cells(crrow, aescolumn).Value = aespn 'set value to proper aes part number
        Else 
            Windows(WorkbookName1).Activate 'activate original workbook
            Cells(crrow, aescolumn).Interior.Color = RGB(256, 0, 0) 
            MsgBox "Part number " & searchterm & " not found.  Cell has been highlighted." 
        End If 
    Next 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Does anyone know how to set a named variable to a value from a cell without using .select on that cell? This is what I tried without success.

	VB:
	
 peaktime = activecell.formulaR1C1 = "=RC[-1]" 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
While I get an error when this line attempts to execute, the variable actually has the correct value assigned to it when I mouse over it in debug mode. If I use that exact same line without the 'set', peaktime equals 'false' instead of the value in the corresponding cell.

Any help is greatly appreciated.
Thanks

Hi all,My looping macro keeps giving me a #NAME? error and I can't seem to figure out what the problem is. I want this code to search a column of data I have. All of the cells contain #N/A error except for one cell that contains a number. I have it looping at the top of the column and working it's way down, checking each cell to see it it contains the one number i'm looking for. Once the number is located I'd like to assign that cell a variable so it can easily be referenced into another equation I have set up in my macro.

Thanks


	VB:
	
 SelectByValue() 
       
    Dim MyRange As Double 
    Dim Cell As Object 
       
        'Check every cell in the range for matching criteria.
        X = 6 
        Do Until IsNumeric(Cells(X, "O")) 
        X = X + 1 
        Loop 
            If IsNumeric() = True Then 
                        Set MyRange = Range(Cell.Value) 
                        Else 
                        Set MyRange = Union(MyRange, Range(Cell.Value)) 
                    End If 
                End If 
            Next 
            'Select the new range of only matching criteria
            SelectByValue = MyRange 
               
    End Function 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I named a cell in a worksheet using the Inset->name->define tool. I can always back track to it using the drop-down arrow in the upper left hand of the worksheet and I know the code in VBA to activate that cell as well.

Application.Goto Reference:="namedcell001"

My question is - how do I set a variable = to the value in that named cell without activating that cell?

similar to var1 = Sheets("Model").Range("R12")

it would read something like this:
var1 = namedcell001

but the above (and several variations of it) does not work.

Good afternoon!

I think I've been staring at VBA too long and can't for the life of me think how to get round this problem.

I'm running a macro from Sheet 2 in a workbook and want to assign a variable to a range on Sheet 3. When I enter the macro, I assign the variable CritRng as follows:


	VB:
	
 CritRng = [Criteria] 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Where Criteria is a Name defined on Sheet 3.

Later on in the macro, I have the following line:


	VB:
	
 CritRng = Range(Cells(TopRow, LeftCol), Cells(CritRow, RightCol)) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
And this line is causing CritRng to refer to Sheet 2, as this is the active sheet. I'd rather avoid using explicit reference to Sheet 3 if possible (in case things are moved around later on), so I'd like to use something along the lines of:


	VB:
	
 CritRng = Sheets(CritRng.Parent.Name).Range(Cells(TopRow, LeftCol), Cells(CritRow, RightCol)) 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The idea being that Criteria is a Range on Sheet 3 and since CritRng = [Criteria] then CritRng.Parent.Name is Sheet3.

But I can't get this to work!

I'm sure this is an easy one, but my brain ain't what it used to be....

Any help with this gratefully received :-)

Many thanks
Darren

Hi Guys,

This is causing me brainache for a Monday so I'm hoping someone can help me solve what I'm sure is quite simple.

I'm creating a userform with MultiPages (not something I'm very used to dealing with), and on each page is a set of comboboxes.

The form needs to be very easy for a third party to maintain so I'm setting up a hidden sheet where an administrator can keep a list of the names of the comboboxes and the possible inputs for each one.

My problem is translating the text on the sheet into a set Object in VBA.

For example on a sheet called "Map" I have in E1, the name of the page in the multipage ("Main"), in F1 the name of a combobox, which is "CatCmb", then in the next column I have the possible values I want to go into the comobobox (G1:G5).

I want the macro to scan across row 1 until it finds the name of the page, then look down down the next column until it finds something and then set that to a combobox object and then fill that object with the stuff in next column to that (See atatched example).

the userform is called OPUS and the Multipage is called DataSet
So far I have something like;


	VB:
	
 OPUSLOAD() 
     
    Dim a As Integer 
    Dim b As Integer 
    Dim c As Integer 
    Dim ListX As msforms.ComboBox 
     
    With OPUS.DataSet 
         
        For a = 0 To .Pages.Count 
             
            With .Pages(a - 1) 
                 
                 'find sheet's column
                b = 1 
                Do 
                    b = b + 1 
                Loop Until Sheets("Map").Cells(1, b) = .Name 
                 
                 
                c = 1 
                Do 
                     
                    If Sheets("Map").Cells(c, b + 1)  "" Then 
                        Set ListX = OPUS.DataSet.Pages(a - 1) & "." & Sheets("Map").Cells(c, b + 1) 
                         
                    End If 
                     
                    ListX.Clear 
                    Do 
                        ListX.AddItem Sheets("Map").Cells(c, b + 2) 
                        c = c + 1 
                    Loop Until IsEmpty(Sheets("Map").Cells(c, b + 2)) 
                     
                     'find possibilities for that box
                    c = c + 1 
                Loop Until IsEmpty(Sheets("Map").Cells(c + 1, b + 2)) 
                 
                 
            End With 
             
        Next a 
         
    End With 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The problem is the line setting the object doesn't seem to be able to do so by combining text off the sheet.

Any help always greatly appreciated.
Many thanks,
Ian

The file never has the same name or is in the same location so I have the macro set up for the user to find the file. That all works well and good.

The problem after that is that I need some generic reference to access this workbook in other subs, because I have to switch between it and other.

Is there any variable type that can be referenced in any sub or something like that?

Heres what I'm using to allow the user to select the file.


	VB:
	
 OpenFile() 
     
    NewFileName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls), *.xls", Title:="Please select a file") 
     
     ' They pressed Cancel
    If NewFileName = False Then 
        MsgBox "Stopping because you did not select a file" 
        Exit Sub 
    Else 
        Workbooks.Open Filename:=NewFileName 
    End If 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Dear all

I require some assistance I am trying to set a variable to find the value of the last two digits of my range(offset(-1,0).

Any help would be gratefully received


	VB:
	
 rngimports = objwsk1.Range("b2:b" & Range("b1000").End(xlUp).Row) 
For Each rngimport In rngimports 
    If Right(rngimport.Offset(-1, 0), 2) = "/x" Then 
        strcells = "20" 
    ElseIf rngimport.Offset(-1, 0) = "/x" Then 
        strcells = "20" 
    ElseIf rngimport.Offset(-1, 0) = "/p" Then 
        strcells = "30" 
    ElseIf rngimport.Offset(-1, 0) = "/i" Then 
        strcells = "59" 
    Else 
        strcells = "01" 
    End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
apologies for not using code tags sorry

kindest regards

We need to do two things in the macro:
1. specifically select the cell for the date recording
2. check the cell first to be sure its empty before setting it to the value
of now()
--
Gary's Student

"Maliniemi" wrote:

> That worked, but when the spreadsheet is reopened, the date/time recalculates
> to the current date/time. I need it to track the date I checked the box and
> stay that way. Also, I associated a cell to show true or false whenever the
> check box is checked, with your fix, it will enter the date in whatever cell
> was last selected. This spreadsheet tracks production events and sums the
> weight of progress to the right. I just have hidden columns doing different
> things in the background and I realy need one to track the date the event
> happened.
>
> "Gary's Student" wrote:
>
> > If there is already a macro behind the checkbox then try adding:
> >
> > Selection.Value = Now()
> >
> > to it. This will stick the value of now() in the range rather than the
> > formula itself.
> > --
> > Gary's Student
> >
> >
> > "Maliniemi" wrote:
> >
> > > Trying to add checkbox to form that enters date in associated cell to note
> > > the date that the box was checked. NOW and TODAY functions recalculate all
> > > dates on spreadsheet everytime it is opened to current date time. Please
> > > help. Thanx.

I'm working in workbook A and am trying to come up with a macro that will look at sheet3 Cell B2 and use that information to open up workbook B and copy the tab with the same same as pulled from cell B2 into workbook A and close workbook B. The problem I can't seem to find anywhere on the net is pulling a cell and setting it to a variable that I can use to open the next piece and use that name for the tab I need to copy. Also little rusty on my excel macro abilities haven't used this stuff in about 5 years.

Thanks,
Bruce

Hi

I've gone throught the internet trying to find a solution, and I can't see whats wrong with the code. The below code copies some specific data from various sources to a sheet called "Grafer 2".

The code does that just fine, but then when it gets to the part about determining what month the cell B6 is, I get an error. The error is "object required".

I would like the code to do the following:

If Month(Ldate) is not December, then it must shift the range "OutstandingData" down a number of rows equal to the month we're in (ie. for September, 9 rows). It should then clear the contents of those rows and set the values equal to the top row of the range "OutstandingData".

The logic is the following:

If the calculation date (CalcDate) is not in December, then we need to create extra rows and put in "fake" data that equals the top row of the "real" data. The real data is described by the range "OutstandingData".

Hope someone can help!


	VB:
	
 
 
wb.Worksheets("Grafer 2").Range("OutstandingData").ClearContents 
wb.Worksheets("Grafer 2").Range("OutstandingData").Value = wb.Worksheets("Outstanding Amount").Range("B3:D305").Value 
wb.Worksheets("Grafer 2").Range("B6").Value = wb.Worksheets("Start").Range("CalcDate").Value 
 
Set Ldate = Range("B6").Value 
 
Month(Ldate) = Dmonth 
 
If Dmonth  12 Then 
    Range("OutstandingData").Offset(Dmonth, 2).Value = Range("OutstandingData").Value 
    Range("OutstandingStart").Offset(Dmonth - 1, 2).ClearContents 
End If 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


I have a need to manipulate IndentLevels in Excel 2007. It is for both visual representation of row headings and for sorting purposes.

I would prefer to automatically have a cell's IndentLevel set according to the value of a cell (next to it). Perhaps something akin to 'conditional formatting'. For example: In cell A1, I have 'Boys'. In cell B1, I have the number "1". I would like for cell A1 to be 'conditionally formatted' to an IndentLevel of 1. In cell A2, I have "Bob". In cell B2, I have "2". I would like for cell A2 to be 'conditionally formatted' to an IndentLevel of 2.

The above would be my preferred solution. However, an alternative would be to have a cell automatically report the IndentLevel of another cell. For example: If cell A2 was intented to level 1 then cell B2 would display the number 1.

I would much prefer for this to be a UDF or conditional formatting, not a macro.

Is this possible? If so how?

Thanks in advance,
Andrew

Hi all, I've been looking for a solution for this for a while now to no avail. I'm trying to define the last row in a column which is determined from a find command. It's quite a large sub, and the rest is working properly, so I'll just post the (still rather large) troubling section.


	VB:
	
 'this sets StartingPoint to a one cell range in a sheet named from a custom class in a workbook named from variable destbook
Set StartingPoint = DestBook.Sheets(ReportTarget.Name).Cells.Find(what:=FullControlName, searchorder:=xlByColumns) 
 
WriteCol = StartingPoint.Column 
 
 'this changes the range to include the whole column of the existing range
Set StartingPoint = Range(StartingPoint.EntireColumn.Address) 
 
 'Problem here: this always sets to one, no matter how many populated cells exist in range startingPoint.
LastRowinDest = StartingPoint.End(xlUp).row 
 
For ii = 1 To 5 
     'loop through columns writing info from original workbook
    DestBook.Sheets(ReportTarget.Name).Cells(LastRowinDest + 1, WriteCol + ii - 1) = ControlCellRange.Offset(0, 2 + ii) 
Next ii 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks for taking a look at this.

Graham Auto Merged Post Until 24 Hrs Passes;

Ok, so I managed to get this working by changing this line:


	VB:
	
LastRowinDest = StartingPoint.End(xl[COLOR="red"]Up[/COLOR]).row 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
to:


	VB:
	
LastRowinDest = StartingPoint.End(xl[COLOR="Red"]Down[/COLOR]).row 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I would have prefered to use the xlup command to avoid spaces, but as I currently have no plans to have spaces in these columns this will work. If anyone has any ideas as to why the first iteration didn't work, please let me know.

Thanks, Graham

My eyes have been opened to the LINEST, SLOPE, and INTERCEPT formulas which are great!

However, in an xy scatter, I can add a trendline and set the intercept to some value (say b=100). Then I can see the equation on the chart y=mx+100.

However, I would like to be able to get that slope(m) in a cell so that I can automatically manipulate it.

I know how to do that if I want to set the intercept to zero by using the LINEST. But I don't want to set the intercept top zero.

....if you are wondering why, it is because I am getting slope ratios for sets of lines - but I need the intercepts of the lines to be equal, setting the intercept to zero in some cases (most cases) is not ideal.....

Thanks

A question occurred to me about setting a variable to the active
workbook. If I set the variable equal to "Activeworkbook", then later
activate another workbook, does the variable now refer to the current
active workbook, or to the same workbook as when it was assigned?

I need some syntax help please…trying to set a variable to the result of this
vlookup formula:

myVariable = "=IF(ISERROR(VLOOKUP($E2,mySheet!myNamedRange," &
myVariablePositon & ",FALSE)),0,VLOOKUP($E2,mySheet!myNamedRange," &
myVariablePositon & " ,FALSE))"

I’ve tried various forms of WorksheetFunction.Vlookup & can’t get it. I
could replace the variable myVariablePosition to a specific column number,
but it is important to me to return a zero in the event of an error.

Ultimately, I want to do something like:

For i = 2 to lstrow

myVariable1 = "=IF(ISERROR(VLOOKUP($E2,mySheet!myNamedRange," &
myVariablePositon1 & ",FALSE)),0,VLOOKUP($E2,mySheet!myNamedRange," &
myVariablePositon1 & " ,FALSE))"

myVariable2 = "=IF(ISERROR(VLOOKUP($E2,mySheet!myNamedRange," &
myVariablePositon2 & ",FALSE)),0,VLOOKUP($E2,mySheet!myNamedRange," &
myVariablePositon2 & " ,FALSE))"

myResult = myVariable1 + myVariable2

cells(i,6) = myResult

Next i

Thanks in advance!

How do I perform a "do loop" using a cell value as criteria? (i.e.-do while
x < value($c$1). Thanks for your help.

It would appear that I cant actually resize an image to a % value. Below is a macro I recorded, would it be possible to set it to a % value, possibly by gaining the initial width and then working out the % from the final width so that I can apply a similar scale to other images?

    Selection.ShapeRange.LockAspectRatio = msoTrue
    Selection.ShapeRange.Height = 53.58
    Selection.ShapeRange.Width = 89.58
Thanks for any help.

Taff

As good programming practice, what of the possible variable types should be
set to nothing or null?
Can /should you set integers to nothing for example?
Thanks
Matt

I want to set the comment to the value of a cell. However when I try to do
this I keep getting an error.

I've tried
ActiveCell.AddComment.ShtRef.Value and
ActiveCell.AddComment.Range("A1").Value and
ActiveCell.AddComment.Cells(R1 - 68, CurMth)
Any ideas

I have the following code that is to set "vlookup_range" to a particular range (it changes throughout the code) - but I cant get it to work - am I missing something? The code gets the debug error on the line where I set the vloopkup_range to the range specified (which is the first range specified in the code)

Code:
Dim vlookup_range As Range 'sets the range to look up customer chosen
Dim lookup_value As String 'to search for customer chosen as a compusafe customer

Worksheets("Branches").Visible = True
    
'COMPUSAFE SLAS
    Sheets("Branches").Select
    vlookup_range = Range("P:P").Select
    
    'LOOK TO SEE IF THE CUSTOMER CHOSEN IS A COMPUSAFE CUSTOMER
    On Error Resume Next
    lookup_value = Application.WorksheetFunction.VLookup(CustomerChosen, vlookup_range, 1, False)
    If Err  0 Then
        MsgBox "Customer chosen is not a CompuSafe Customer.  CompuSafe SLAs will not be extracted"
    Else
I used to have which seemed to work a little better but still failed:
Code:
    Sheets("Branches").Select
    Range("P:P").Select
    set vlookup_range = Selection


What is the difference between this line:

Vs This line:

I guess outside of code the question would be what is the difference between:

Set X = Y

Vs.

X = Y

This is a logic based macro that needs to compare values in 1 column above a specific cell and take action until the column has no more data. Here's what I'm trying to do (and I've also attached a sample of desired results):

1) I want user to input starting cell. So I need to create a button for that.

2) This is where the logic begins and I'm having difficulty.
From cell defined in button, I move up one cell in that column and compare that value to the original cell.

If the value is greater than original cell then logic needs 2 outcomes:

a) move up 1 cell in column and if that value is less than original cell, then extract that value and copy that value one cell to the right or 1c.
b) move up 1 cell in column and if that value is greater than original cell, then move up again, until find lesser value than original cell. Once we find value less than original cell, extract value and copy value one cell to the right or 1c.

Once lesser value has been copied to new cell, logic is now:
a) move up 1 cell in column and if that value is greater than original cell, then extract that value and copy that value one cell to the right or 1c.
b) move up 1 cell in column and if that value is less than original cell, then move up again, until find greater value than original cell. Once we find value greater than original cell, extract value and copy value one cell to the right or 1c.

At any point in the process if we find a value equal to the starting cell, we ignore it and the logic continues. The logic needs to continue flip flopping like this to top of column.

OR
Here's the logic now in reverse I need:

If after moving up 1 cell of original cell, the value is less than original cell then logic needs 2 outcomes:

a) move up 1 cell in column and if that value is greater than original cell, then extract that value and copy that value one cell to the right or 1c.
b) move up 1 cell in column and if that value less than original cell, then move up again, until find greater value than original cell. Once we find value greater than original cell, extract value and copy value one cell to the right or 1c.

Once greater value has been copied to new cell, logic is now:
a) move up 1 cell in column and if that value is less than original cell, then extract that value and copy that value one cell to the right or 1c.
b) move up 1 cell in column and if that value is greater than original cell, then move up again, until find lesser value than original cell. Once we find value lesser than original cell, extract value and copy value one cell to the right or 1c.

Again, at any point in the process if we find a value equal to the starting cell, we ignore it and the logic continues. The logic needs to continue flip flopping like this to top of column.

I hope someone out there can understand this. Been trying very hard to figure this out, but can't seem to get it. I understand the logic but trying to program the macro is proving more difficult than expected. Attached are 2 files demonstrating showing how the logic needs to flip back and forth as moving up the column depending on the first value above original cell.

I have got multiple records displayed on a userform at present, where traversing through a for loop creates the required amount of TextBox's at runtime displaying the current data from the associated cells.

I want to display the records in the userform so via TextBox's so that the user can view their input and ammend where necessary.

My problem is trying to identify each approriate TextBox to the concerning cell.

Here is a snapshot of some of the necessary code to go along with my description above -

' Information from worksheet
Set Data = Sheets("Input").Range("H5:H100")

' Cycle through all records in Sheet1
Records = Application.CountA(Sheets("Input").Range("H5:H100"))

' For all the relevant records
For i = 1 To Records

' Set variables to relevant values from 'input' sheet
ImpactEntry = Data.Cells(i, 3).Value
' Creates a new label

Set NewText = Activity1aEdit.Controls.Add("Forms.TextBox.1", "txt" & i)
' Label properties
With NewText
.Name = "FieldLabel" & i + 1
' Include variable values within caption
.Text = ImpactEntry
.Top = 360 + (70 * i)
.Left = 150
.Width = 100
.Height = 200
.MultiLine = True

When they ammend the input I want to set the Data.Cells(i, 3).Value equal to the concerning TextBox value.

As you can see, I tried naming the TextBox's that are created as - ("Forms.TextBox.1", "txt" & i)

Then I did

Data.Cells(i, 3).Value = Activity1aEdit.Controls("txt" & i).Text

but alas it did not work it stated - Could not find the relevant object.

I would be greatful for any help or advice.

Thanks.


No luck finding an answer? You could always try Google.