I'm trying to test whether a variable, a range in this case, has already been defined. If not I want to Set it to a specific cell.
I tried:
If rng Is Nothing Then
Set rng = Range("B14")
Else
rng = rng
End If
I am trying to determine Long Term Gain (LTG,) Long Term Loss (LTL,) Short Term Gain (STG,) Short term Loss (STL,) or No Loss nor Gain (NGL)testing two cells (A1 and B1)and setting a third cell (C1) to the text LTG, LTL, STG, STL, or NGL depending on the results of testing cells A1 and B1.
A1 represent a number of years and B1 represent gains or losses (negative)in dolars.
The way I see the logic is as follows:
If cell A1 or cell B1 are either one of them equal to 0, then it is neither a Gain nor a Loss (NGL.)
If cell A1 is greater than or equal to 1, then it is Long Term; else, if A1 is greater than 0 and less than 1, then it is Short Term.
On the other hand, if cell B1 is greater than 0, then it is a Gain; if B1 is less than 0 (a negative number,) then, it is a Loss.
I need to find (if it is posible in Excel) one formula to test the two cells for posible outcomes:
If A1 = 0 then C1 = NGL
If B1 = 0 then C1 = NGL
If A1 >= 1 and B1 > 0 the C1 = LTG.
If A1 >= 1 and B1 < 0 the C1 = LTL.
If A1 < 1 and B1 > 0 then C1 = STG
If A1 < 1 and B1 < 0 then C1 = STL
I need to test whether an optional Range has been passed to a UDF. IsMissing(RangeName) always returns False regardless of a range being given or not. RangeName exists as an Object that shows a Value of Nothing and Type of Range. I have not found any test that will indicate if the Range was passed in or not.
View 4 Replies View RelatedI am trying to build a macro that test for the value of each cell of a range (in a column), and if found, then the value of the cell of the same row (another column) will be set to 1. If not, then the macro writes a formula to get some data from BBG (this part is ok). this is what I have done so far but I have an error message, telling me "Not Else with out if" .
Code:
Sub Fx()
With Worksheets("DivRelease")
Dim LastLig As Long
[Code].....
I d like to test for the existence of text in range D5 to D10000 for example
I may have this 49000897890VGN21SP. VGN21SP is text so I d like to spot it
it may also exist on its own VGN21SP can you suggest me with a macro?
like this one
Sub DeleteNAs()
Dim C As Range
For Each C In ActiveCell.CurrentRegion
If C.Text = "#N/A" Then C.ClearContents
Next C
End Sub
Is there a way to check if a named range exists before I run a piece of code? I created a new file that has need for all the old file's ranges plus a couple more, and I want to use the same macro for both. So on the first file I just want to say, if these other named ranges are there, go ahead and do his other thing.
View 6 Replies View RelatedI have a range of data that contains can contain either a pass or fail. what I require at the bottom of this range is a formula that says 'if any one of the range = fail then "test fail", else "test pass"'
View 2 Replies View RelatedI need to do stats for each month within a set of data. Ie take the min for all data in the month of Jan only and repeat for all other months etc. Is there some way I can set up a Min function to only consider the data belonging to jan for example and have it change and only consider Feb, then march.. etc Seems like it would be easy to just do this manually but there is a large timeframe over which data was collected so would take forever.
Have attached sample of data. rainfall.xlsx‎
I want to randomize a range of values stored in sheet 1 and insert them in another cell.
I am having values for sales consisting of product names and its price. there are 10 product names and its corresponding prices and is stored in cells E1:F10. I want to generate test data containing product names and its corresponding price. The generated data needs to be saved in the cells A1:B50
the product names needs to be randomized. This needs to be done in vba. Below is the screenshot of the final result that is needed. As you can see, I have randomized my 10 products and its prices into the test data column. This was done using vlookup formula and I need the same to be done in vba.
How do you write an If then statement using a range of cells? I want to construct a logic test using the range of cells A7:A19. I want the logic test to see if the any of the values =1. If the test is true then I want it to display the value in B7:19 (which ever cell corrulates to the cell in column A that has the value of 1) and display the number in the B column.
View 6 Replies View RelatedOn Sheet1, Row 2 I have my columns named January-December, which correspond to the names of the other 12 sheets in my workbook. I want to test and sum the same range of cells in each sheet by simply coping and pasting the formula. I am using INDIRECT and SUMPRODUCT, but is there a better way? It seems to have caused the sheet to run slowly. Perhaps it's just the amount of data. Here is an example of the formulas I am using:
=SUMPRODUCT((INDIRECT("'"&B2&"'!$B$3:$B$200")=$A$2)*(INDIRECT("'"&B2&"'!$C$3:$C$200")="Yes")*(INDIRE CT("'"&B2&"'!$E$3:$E$200")))
This gives me the intended result. B2 in this case is January. C2 is February and so forth.
Using Excel 2013,
It trying to see if the Month() of a date is in a Array / Range
I tried =IF(MONTH(E8)={2,6,9,11},"Yes","No") where E8 = 9/30/12
How can I test in my VBA wether a named range (with a scope of sheet) exists on the active sheet?
Code:
If "EmployeeEmail" exists on the active sheet Then,
xxxx
Else
yyyy
End If
Using Excel 2010.
Say you define a public range variable called Inputworksheet and you set it to refer to the worksheet called Inputworksheet. You have a separate string variable with the value Inputworksheet. How do you get this string variable value to call/control the range variable Inputworksheet?
I am getting an excel worksheet value from a lookup function that corresponds to the name of a VBA range variable. Once I have this worksheet value, I would like to use the range variable that has the same name as the worksheet value.
I am trying to autofill dynamic ranges that have column variables (d) and row variables (x)... I am having a hard time with the syntax on this
View 9 Replies View RelatedI am using a variable named " Totals" as a range type to refference the range in a formula. It works the way I have it.
Dim Totals As Range
Set Totals = [U37: AE37]
Now instead of the absolute refference, I would like to change the row refference by an offset of my current row, using a formula with a varriable. The columns stay the same.
I am trying to write a macro which will autofill specific columns. The macro will set the range from the start of my autofill to the end of my autofill as a constant range.
The problem I need to get around is the end of my range can always change each time I run the macro. For instance, the first time I run the macro I may only need to autofill from row 4 to row 15. The next time, I may only need to autofill from row 4 to 23 (because of user updates). How can I make the end of my range not be a constant address but variable?
I am trying to write code to select a range in a worksheet where the last cell in the range is variable.
Sub DataTest()
Dim LastColumn As Integer
Dim LastRow As Long
Dim LastCell As range
[Code].....
I have a named range, called SubjectNamesPastoral on a worksheet called Worksheets("Group to Teacher")
I can't assign the named range to the rngSubjectFamilyRangeOnSubjectUsedSheet variable in vba.
the first two lines of code work fine, the msgbox shows "E100:E105", happy days!
However when I try to assign the same range to the rngSubjectFamilyRangeOnSubjectUsedSheet variable, the debugger runs past the 'Set' line without error, but throws 'error 91' at the second msgbox.
VB:
thisString = "SubjectNames" & strSubjectFamilyOfGroup
MsgBox Range(thisString).Address
Set rngSubjectFamilyRangeOnSubjectUsedSheet = Worksheets("Group to Teacher").Range(thisString)
I am using the code below to copy a range and paste it over a variable range.
View 4 Replies View RelatedI have been working on part of the code for my spreadsheet and it works fine in the spreadsheet “Databaseform” however when I copied the code to my master spreadsheet “Paul_PartLocDBCombo” it does not work, I get the error:
Method ‘ range’ of object ‘_worksheet’ failed
The code is then highlighted in yellow, the code is:
Set rng = wksPartsData.Range("a1", Range("a65536").End(xlUp))
Meaning this part is incorrect but I don’t know why? To work it: go to Databaseform and press start. Enter 7mm in the product field and press find all. It will then return all the matching results in the userform. Its this I want to try and achieve on the other spreadsheet when the button find label is pressed.
I have a range "C1:Cx" where i want x to be the value from a value "overallLastRow". How do I write this?
This is just a simple question but I can't seem to find the answer in any of the hepl files. I want to refer to a range by using a variable but can't get the syntax right.
for example instead of
I am in need of code to sum the above range. But it is different range every day. My company will add the deposit amounts each day from each deposit sheet.
ie:
48000
80000
12000
3000
200
45000
10000
And so on. I have posted an example spreadsheet if needed. (The portion is green is all that needs to be summed but keep in mind it changes amount of rows included changes each day, and the yellow is where the solution goes, but I can easily change that). There are other formulas needed but I have figured those out. I need to run this macro daily just for the last group.
I have a problem of setting a range of row that might expand regularly.
I tried a simple method but...
Code:
' Count the used range row
Dim count As Integer
Dim usedrow As String
count = ActiveSheet.UsedRange.Rows.count
usedrow = "C" & count
' The items are in A1:A105
Set AllCells = Range("C4:usedrow")
I left my Excel books at home, so I can't even look up the answer, and it's incredibly frustrating, since I know it's something small that I'm missing.
I've got a code that I want to cycle thru 12 ranges, named "Retrieve1" thru "Retrieve12". This is the snippet of code that I'm having the issue with is:
For I = 1 To X
NextRange = "Retrieval" & I
Range(NextRange).Select
Application.Run Macro:="EssMenuRetrieve"
Next I
I only made the "NextRange" variable because I originally had "Retrieval" & I in the range select statement and it didn't work.
I am using this coding
LR = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row
Range("A" & Row.counts & ":" & "D" & Rows.Count).Copy Destination:=Sheets("Sheet2").Range("A" & LR + 1)
And it keeps giving me the error "Object required"
I am trying to copy from A-D and the row is a variable to which row was checkmarked at that time.
I am trying to create a spreadsheet that will predict the quantity of a product sold in a given month based on the number of new clients that month and the frequency with which the existing clients will need that product to be replaced. E.g. Washing Up Liquid is required by every new client and they will need a new one each month. Toilet Cleaner is required by every new client and they will need a new one every 3 months.
I have 'hard coded' the two examples into the formula on the "Non-Variable" sheet. However the Frequency of Replacement is speculation; it may traspire that a bottle of Toilet Cleaner only lasts 2 months, in which case I would have to go into each cell and change the formula to reflect this. I believe Add or SUM Every nth Cell may hold the answer but I am failing to get it to work with my data due to being a novice and not really understanding the formulae used! The second sheet is my attempt.
As you can see, it counts 3 months forward from the first month, not 3 months BACK from the CURRENT month. It also simply doesn't work: Washing Up Liquid should equal 285. Ideally I would like to keep the format of the 1st sheet as this matches the rest of the the workbook (not included).
still trying to figure it out how the logic in VBA works
'this selects a block of sells and put them in variable rormu. Right?
Set test = ActiveSheet. Range("i65535").End(xlUp).Offset(-1, -3)
Set test2 = ActiveSheet.Range("i65535").End(xlUp)
Set formu = Range(test, test2)
'here i try to create another variable for a range after last used row
Set newg = ActiveSheet.Range("f65535").End(xlUp).Offset(2, 0)
Set newg2 = ActiveSheet.Range("f65535").End(xlUp).Offset(3, 3)
Set pst = Range(newg, newg2)
I have reached a point in my code where I have a variable range selected. I want to autofill the selected range of data to a new range.
My code is:
Range(Cells(7, LastReportColumn - 1), Cells(LastReportRow, LastReportColumn - 1)).Select
Set DestinationRange = Range(Cells(7, ReqMonths + 5), Cells(LastReportRow, ReqMonths + 5))
Selection.AutoFill Destination:=(DestinationRange), Type:=xlFillDefault
The select part works. However on the second line (set DestinationRange) I get a type missmatch.