I am trying to make the "A4:A" portion of this line of code generic so it can work in multiple different files. I have defined a variable FirstGridRow that will take care of the '4' but I need to find a way to splice it all together. Essentially I am stuck trying to tack on the ":A". The first piece shows where I am coming from and the second is where I am trying to go to.
View 3 RepliesIt's just meant to work through a long list of data blocks defined by column 8 changing. At that point I want to calculate the average of the first 20 entries in columns 12, 13, 14, 15, 16, 17 in that particular block.
I can achieve the sort and calculate the block start rows OK, just can't get the average definition right.
Code:
Option Base 1
Dim c(6)
Sub calc_averages()
[Code]....
I sum number of articles that are coming in on one sheet and articles going out on another. The total of In - Out is a separate value. All three values are placed on a third sheet.
The following code I use does the job, but I have to use a set range in the SumIf function. I want to be able to use a variable for the lastrow of a table colum.
VB:
Private Sub ArttotKnop_Click()
Dim Klanttel As Integer
Dim Rij, vLastRow, iLastRow As Long
Dim varResult, varMin, varTotal As Variant
iLastRow = Sheets("Ingebracht").ListObjects("Ingebracht").ListRows.Count
[Code] ....
Since my table grows larger every day I want to use variables.
Where I wrote A2:A500, B2:B500, B2:B550 and D2:D550 I want to be able to replace A500 and B500 with A and B + the variable iLastRow. The same for B550 and D550 with the vLastRow variable
Tried using:
.Range("A2:A" & iLastRow)
.Range("A2", Cells(iLastRow, 1))
.Range(Cells(2, 1), Cells(iLastRow, 1))
They don't work.
I'm trying to develop a work spreadsheet template that is to be summarised every fortnight. Our fortnights here go Thurs-Thurs. So I figured I would start with a base date (thurs - Sept 4th) and then have the spreadsheet display the true fortnight ending upon loading. Ultimately this fortnight ending will be used to copy and paste the template information into a summary spreadsheet but for now I am trying to get this intial part working. It's linked to a command button and my attempt at code is below:
Private Sub CommandButton1_Click()
Dim Base As Date
Dim Now As Date
Dim Delta As Integer
Dim No_of_Fortnights!
Dim Rounded As Integer
Dim Fract!
Dim Fort_week_end As Date
(Note the following 8 lines are not the problem, they just open the main sheet. The problem is the eights lines starting with the word 'set')
Sheets("Technical Inquiry Form").Activate
Sheets("Technical Inquiry Form").Range("b3").Select
ActiveCell.FormulaR1C1 = "=TODAY()"
Sheets("Technical Inquiry Form").Range("b3").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Technical Inquiry Form").Activate
What the code does is that it runs in a Master workbook, and searches for XLS files in a defined folder. It opens a file, count, closes the workbook and takes the tallied number of sheets and places it in the master file, column A being File Names and column B as Number of sheets. What I need is for the code to take the file name from column A, run it and then place sheet counts in Column B and then move down the columns until all the counting is done. Currently I am only able to define one row using the range function and i'm at a loss.
'Get file names and enter into Master
Public Sub ShowFolderList()
Dim wb As Workbook
Dim ws As Worksheet
ChDrive "C"
ChDir "C:Documents and SettingsTest Folder"
outrow = 2
Filess = Dir("*.xls")
While Not Filess = ""
Cells(outrow, 1).Value = Filess
outrow = outrow + 1
Filess = Dir()
Wend.......................
Using the SpecialCells method with a Type = "xlCellTypeFormulas", I can identify the range contains cells with formulas.
But when there isn't any of these cells in the range you've applied the SpecialCells method to, you get a VBA Error 1004. (Doing this outside VBA gives you a "No cells found" box.) How can I skip over this error?
I'm looping through each sheet in the workbook, and defining the UsedRange. I'm doing another loop that uses the HasFormula property on each cell and then performing an action if HasFormula = True, but this results in a lot of extra work processing cells that don't have formulas.
I'd like to further refine the process by defining the range of cells on each sheet that have formulas. But if a sheet contains no cells with formulas, I'd like to skip past it and move on.
I think this is just a simple syntax / code construction question.
I started plugging away at VB a little over a month a go and i feel i've learnt alot but can see just how limited my knowledge is. how to define all my variables in one module that then will feed into the code on other modules and forms in the workbook where required. For example, my spreadsheet has approx 30 different variables that are called upon by a variety of subs. How do i define all these in one place and then just call on the relevant ones when required rather then having to redefine them for every new sub.
View 2 Replies View RelatedI'm trying to loop through a range in excel from access, checking where the titles (in Excel row 1) match with the fields (in a recordset in Access that is passed to the function) - and where they do, I want to dimension a variable to hold the column number - I'm not sure it's possible, but I'd be interested to know either way. The line I'm asking about is at the bottom of the code - the rest of the code is just to give context...
Sub ImportGeneric(rsImported As ADODB.Recordset, rsConfirmed As ADODB.Recordset)
Dim fd As FileDialog
Dim xl As New Excel.Application
Dim wb As Excel.Workbook
Dim ws As Worksheet
Dim iFilePicked As Integer
Dim strFilePath As String
fd.Filters.clear
fd.Filters.Add "Excel files", "*.xls"
fd.ButtonName = "Select"
iFilePicked = fd.Show
If iFilePicked = -1 Then
strFilePath = fd.SelectedItems(1)
Else ..................
I wrote my function as it is shown below:
Function MyFunction1(r, n, xrange, yrange)
If r <= n Then
For i = 1 To 5
MyFunction1 = MyFunction1 + xrange(i)
Next
Else
For i = 1 To 5
MyFunction1 = MyFunction1 + xrange(i) * yrange(i)
Next
End If
End Function
This is just a very basic idea of what I need to do, so it might not make sense why I am doing it, but I am just trying to test that the function works. Anyhow, the thing is that it works with something like this when I input it in a cell in a worksheet: =MyFunction1(RAND(),0.5,{5,6,7,8,9},{10,20,40,50,100})
However, if I try to define the range or set {5,6,7,8,9} with other cells, it does not work. so for example if I try the following:............
Can a Function give two or more output variables. e.g.
Sub a()
x = 5
result = Y(x)
End Sub
Function Y (x As Integer) As Integer
Dim B
B = ... * x
Y = ... * B
this will give back Y as a result. But if I want to get 2 or more output variables (let's say I need to get also B into sub) from one function, how should I do that?
I need this because function works with large matrix and I want to extract some values appeared in between.
I'm trying to use a range(Cells1),(Cells2) all working fine, but now I'm trying to use variables for the Row selection. selection.row as one of the variables, but keep on getting a error.
Code:
Sub Clear_Time()
Dim Rng As Range
Dim pointer As Integer
pointer = Worksheets("Summary").Range("Z1").Value
Worksheets("SubPanel").Range((Cells(10, pointer)), (Cells(52, pointer))).Find(What:="").Activate
[code].....
I have a drop down list in a column called Report Type (example below).
Report Type - Drop-Down Menu in Column F
Business/Operational/Work Plan
Budget Report
Performance Report
Program Quarterly Report
Program Mid-Year Report
Program Annual/Year-End/Final Report
Service Quarterly Report
Service Mid-Year Report
Service Annual/Year-End/Final Report
Financial Quarterly Report
Financial Mid-Year Report
Financial Annual/Year-End Report
Auditied Financial Statements
In-Year Reallocation
Annual Reconciliation Report
SRI Report
Other Report
I need to count all the cells that have: Budget Report, Financial Quarterly Report, Financial Mid-Year Report, Financial Annual/Year-End Report, Audited Financial Statements, In-year Reallocation, and Annual Reconciliation Report
Is this possible with a countif formula?
I would appreciate knowing how to fix this formula to do more than less than/more than answers. How do I have the following answers return for these birth year ranges? I have attached a sample file, with the formula in place. Thank you so much for any help!
1974-1991 = amateur 18-35
1900-1973 = amateur 36 and up
1992-1994 = junior 15-17
1995-2005 = junior 14 and under
blanks or zeros = unknown
I'm having an issue trying to locate a certain value in a cell.
If it is a number, say 50, then my code works, if its a word though
then it doesnt work. How can I get this to search with a list of words rather then numbers?
Sub alpha()
Dim cell As Range
For Each cell In Range("A2", ["N27"])
If cell.Value = Beff Then cell.Interior.ColorIndex = 6 'doesnt work
If cell.Value = 50 Then cell.Interior.ColorIndex = 6 'does work
Next cell
Range("A2", ["N27"]).Font.Bold = True
Range("A2").CurrentRegion.Copy
Sheets("Sheet1").Select
Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
End Sub
I'm having some issues getting a tool I'm working on to function correctly. I have some cells, the number of which can vary based upon user requirement, that contain cell addresses. What I need from VBA is to translate these addresses into usable row, column values for the user defined range. Example:
Cell D4 contains F14, which was input by the user.
What is the best way to pass this data through VBA. Right now I am currently using:
Code:
Dim strRange as String 'left out loop parameters
dim TempRange as Range
strRange(i) = Cells(4, 4) 'using TypeName on StrRange at this point yields a string result as expected
set TempRange = Range(strRange(i))
From my understanding that should work but I am getting type mismatch.
I do not have any reason for actually using a string, but would like the user input to only be 1 cell. If there are cleaner ways around this, I am open to them.
Then from the range, I would have two other variables as type long for storing the row and column data using:
Code:
Pathing_Rows = TempRange.Row
Pathing_Cols = TempRange.Column
I am trying to define a dynamic range based upon error criteria. After the first error, all the subsequent cells are filled with the error and I would like to limit the named range to the rows with no errors (one column wide). I am thinking something similar to (realizing this is probably very wrong):
=OFFSET( 'Basin Routing'!$X$5,0,0, COUNTIF('Basin Routing'!$X:$X,AND(NOT(ISBLANK()),NOT(ISERROR()))),1)
I am trying to select a range based on two variables which store the column numbers. what I have is:
View 4 Replies View RelatedSo I have a spreadsheet with a list of companies, list of users within those companies, and the status of those users(Active/Inactive/Deleted). I'm trying to determine the company-level status based on how the overall status of all the users in the company.
E.g.
Code:
A B C D
Company A User 1 Active
Company A User 2 Active
Company A User 3 Inactive
Company A User 4 Deleted
Company B User 1 Inactive
Company B User 2 Active
Company B User 3 Inactive
In the above example, cells D1 to D4 would list "Active", since Active users form the bulk of the company. D4 to D6 would list "Inactive" for Company B. I'm trying to use a formula to automate this for the whole spreadsheet (5,000+ rows)
I've figured out the first half of what I need to do:
Code:
=IF(COUNTIF(A1:A4,"Active")>COUNTA(A1:A4)/2),"Active",IF(COUNTIF(A1:A4,"Inactive")>(COUNTA(A1:A4)/2),"Inactive","Deleted"))
My problem is in getting Excel to automatically define the cell ranges according to the cells that contain "Company A", "Company B", etc. I have over 5,000 rows on the spreadsheet so having to manually change the cell ranges for each company would take forever.
I have a workbook with several worksheets in the same format. I would like to have a function to output the sheets that have rows in which collumn A = X AND collumn B = Y.
I've been working on this one for a couple days now and I'm not making much progress.
I'm having trouble with my logic again :/
AREA
Tp std
Tp
30
5
So I want the Tp in the 3rd column to show the result:
IF AREA is less than or equal to 20, Tp=Tp std / 2
IF AREA is between 20 and 40, Tp=(((AREA-20)/(40-20))*(Tp std/2))+(Tp std/2)
IF AREA is 40 or greater Tp = Tp std
But as one equation
I have just been struggling with the range part.
I ahve some range of cells in one Sheet and I have one cel in anonther sheet which will hold the sum values in the range.
View 9 Replies View RelatedI have and Indirect function that works.... I need to modify it to include a cell address reference, but this requires the use of a Vlookup function to find the address ....
I have this formula: but it does not work
I'm not sure how to include the VLOOKUP function in my argument to
[/quote][/code]=INDIRECT(CELL("contents",B3)&"!&Address(VLOOKUP(B16,'1'!B:AE,match(RAY_ANALY!D2,'1'!B2:AE2,0),0)")
How do I use the ISERROR function on a range. I have tried =ISERROR(A1:A10) but this doesn't work, it always gives the "true" output even if there is no error in that range.
View 4 Replies View RelatedI need to figure out a vlookup function that searches for two variables and return an answer. such as the example attached;
vlookup -- 12253 & 605.34 (if these are matching then) 131001
I am creating a worksheet and I have a formlua where the value is text. I can make it work standing alone but not with other variables. =IF(E11=S1,0)+(IF(E12=S1,0)+(IF(E13=S1,0.0025))+IF(E14=S1,"Call for Prciing"))
View 2 Replies View RelatedI am using VB to insert a function into a cell on an excell worksheet. The relative references work fine. For the absolute reference, I want to use a named range (LowTotal). If I simply put the name into the function, then I just get the name as text in the function. The second insert formula is were the named range is intended to go. Here is the code so far...
I need to know how to use the varriable name in the .formulaR1C1, so that VB knows it is a variable and not just text....
Basically, I have a worksheet that shows test results for 6 separate tests. If an objective is met in a test then it is marked with an "X". If an objective is partially met then it is marked with a "/" and if it is not met then it is marked with a "." . I have now created a combined column where I want to collate whether an objective has been met at all across the 6 tests.
For e.g. if in the range H14:M14 there is an "X" then N14 should show "X". If there is no "X" but there is a "/" then N14 should show "/" and it there are neither values in the range then it will show "." .
I tried
[Code] ....
but it just came back with a formula error.
I'm trying to write some code that inserts a SUMIF function to a given range of cells:
In simplified form, the code runs as follows: ...
I am trying to use the SUMIF function in excel where I want to sum a range of cells greater than whatever is in cell D8. Here is what I tried to use, but it doesn't work. SUMIF(A2:A10,>D8,B2:B10)
The content in D8 can change because the user makes a choice from the drop-down list and a number pops up in D8 that is referenced to their choice.
I am having a hard time getting my match functions to work with letters and numbers in the same range. I am also trying to figure out a way to have the ranges switch if there is an error. because the data is in multiple columns. It is hard to explain so I have attached a sample workbook.
ExampleFile.xlsm