# Create Active Cell Formula Based Of Variables From Given Cells?

Apr 20, 2012

I am trying to create an active cell formula based of variables from given cells.

With ActiveCell.FormulaR1C1 = "=CONCATENATE(""RU"",RC[-12],RC[-11],RC[-10],RC[-9])-CONCATENATE(""MU"",RC[-12],RC[-11],RC[-10],RC[-9])"
ActiveCell.Formula = ActiveCell.Value

But This does nothing.

## Excel 2010 :: VBA Repeating Formula(s) In Specified Cells Based On Defined Variables?

Dec 28, 2012

In my worksheet, I know the last line of my report AND I know how many "reporting segments" (all equidistant and identical) there are in the report based on the last row using the below (which I tested in several reports in messages):

VB:
Dim vListEnd As Variant
Dim vTaskCount As Double
vListEnd = ActiveSheet.Range("A1").Offset(Sheet1.Rows.Count - 1, 0).End(xlUp).Row
vTaskCount = ((vListEnd - 5) / 22)

My "reporting segments" are ALWAYS 22 rows each and the first one always starts in row 5. Example: A document where the last row (containing an End of Report special character) is 247 would have 11 reporting segments.

Within each reporting segment, there are two different formulas needed.

For the first reporting segment located in rows A5 thru J27:

I need FORMULA1 in all cells of the range (D5:D15 dragged out to H5:H10(not 15) and D18) and FORMULA2 in cells (F20:F22,J20:J22).

Then I need to repeat that vTaskCount number of times (number of segments) and at intervals of 22 (segment repeater offset). Some reports may have 2 segments, some might have 50, but the structure is always identical.

My formulas are all pretty simple if it makes a difference (originally using FormulaR1C1 = "=RC[-1]+RC[-3]" --- but because I now know solid cell locations I suppose I don't need to make them relative, too.)

## Macro: Create Directory Based On Active Workbook And Save To It

Sep 8, 2006

Read �My Documents� Path And Use Result

Problem:

Note:

Typical user OS will be Windows XP Pro / Win 2K
Excel version : 97 / 2002 / 2003

1. Corporate network security settings will only allow directory/subdirectory creation in the �My Documents� section of customers individual computers.

2. Per customer request, VBA application needs to save extracted files for future use.

3. I can specify an initial �My Documents� subdirectory be made and the VBA application file be loaded/copied into that location � i.e. � �My DocumentsCat�.

4. When VBA application is opened from that specified directory, (first time), the application needs to make an additional subdirectory tree to save future files. I can read the opened from location via VBA with the following:

Dim filepath As String
filepath = ThisWorkbook.Path
As an example � this code would produce a string definition of �filepath� � such as the following:............................

## Formula To Create Blank Cell Based On Dollar Amount In Another Cell?

Jan 7, 2014

Ok, basically C3 is a dollar amount. The default total for C3 is \$0.00. If C3 is \$0.00 then this formula cell will display a blank cell. If there is any other amount in C3 then the formula C3-C2 will run.

I tried this but it's not working:

=IF(C3="0","",C3-C2)

## Create Range Based On Starting Cell Plus A Number Sent To Formula

Mar 13, 2014

I'm using the VBA code below for a piece of code.

[Code] .....

However, I want to use this same function in another place, without the limit set on the cells that will be cleared.

What I would like to do is send the formula the cell to start at (E14), offset that by one column, and then send a number of rows. With that, create the range to be cleared.

Something like:

[Code] .....

## Use Active Cell To Create Hyperlink

Oct 7, 2008

I am trying to use an active cell (i.e. "Sheet2!E7") which always changes, to create a hyperlink on a different sheet to that particular cell. What is happening is I have a master list of current open POs. On a separate sheet I have the PO listed in greater detail. I want the PO number on my master list to have a hyperlink that brings you to my detailed PO on another sheet. Simply, I need a way to turn my active cell into text (not the contents, but the cell itself).

## Create Hyperlink In Active Cell

Dec 2, 2009

I'm running Excel 2003 and I'm looking for assistance with programmatically creating hyperlinks. I have to update a Shared Workbook on a weekly basis. The cells in column D contain a catalog id and I need to create a hyperlink that includes this id in the url.

What I want to do is insert a new row, enter the catalog id and then create a hyperlink that contains the id. Additionally, the text to display has to be the 8 digit id. Here is an example of the hyperlink.

## Assign Formula Values Based On Variables

Jun 6, 2007

I have three variables; Independent variables x & y, and dependent variable z. x & y can take the values 1,2,3 and can equal each other z can also take on values 1,2,3 but can never equal x or y. I tried using if/and statements to solve this (see below), but I would have to do it for each case and it exceeds Excels embedded function limit.

=If(and(B7=1,B10=1),riskduniform(E4:E5),if(and(B7=1,B10=2),3,if(and(B7=1,B10=3),2,if(and(B7=2,B10=2),riskduniform(1,3),if(and(B7=2,B10=1),3,if(and(B7=2,B10=1),3,if(and(B7=3,B10=3),riskduniform(1,2),if(and(B7=3,B10=1),2,if(and(B7=3,B10=2),1)))))))))

## Automatically Select From Dropdown Based On Variables In 2 Other Cells

Jan 6, 2013

I have a sheet where I have a "Status" column in each row which could be "Waiting for Band", "Waiting for Organizer", or "Ready to Go!". These options are set as a data validation list. I have 2 cells later in each row where the response can only be "Yes" or "No". I want for the Status column to be set automatically based on the responses in the later two cells. So for example

where C3 and M3 are Yes, the status column in B3 should read "Ready to Go!"
where C3 is no but M3 is yes, the status column should read "Waiting for Organizer"
where C3 is no and M3 is no, the status column should read "Waiting for Band"

The above is actually an exhaustive list of all of the potential outcomes(!)

## Variables To Contruct The Name Of The Active Array

Jun 24, 2008

I am trying to construct an indirect array reference as follows;

Dim A10 As Variant
Dim A11 As Variant
Dim A12 As Variant
For i = 0 To 2
String1 = "A" & (10 + i)
String1 = Cells(6, D1_CNum + i).Resize(2995, 1)
Next

But the above doesn't work, what would be the correct way of creating a reference to an Array using variables to contruct the name of the active Array?

I was hoping String1 would equal "A10", so that on the Cells command Array A10, A11 and A12 would be filled using the value of String1??

## Countif Formula For Multiple Variables In The Same Range Of Cells

Jun 4, 2014

I have a drop down list in a column called Report Type (example below).

Report Type - Drop-Down Menu in Column F
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?

## Nested If Statement - Calculate Value Based On All Of Active Cells In Column

Sep 11, 2013

I am putting together a procedure to calculate a value based on all of the active cells in column "A" and a couple other conditions. "k" is my variable to put a value into (k,8) until all the active cells in column A have a corresponding value in (k,8)

I have my scenarios all built out into If statements and the code runs....but no value is being added to cell (k,8) for any of the rows with entries in column "A" when the procedure is finished running. Depending on the values in (k,2) and (k,3) there are four different ways the value in (k,8) needs to calculate as I have built out below. Why my values aren't being added to (k,8)?

Code:
Sub CalcSpecialFee()
Dim k As Integer
Dim cell As Range
k = 4
Application.ScreenUpdating = False
Sheets("Summary").Select

[Code] .........

## Merge Cells: Select A Range Based On Two Variables Which Store The Column Numbers

Jul 19, 2009

I am trying to select a range based on two variables which store the column numbers. what I have is:

## Asigning Variables Based On Cell Content

Sep 30, 2007

I just ordered vba for dummies, in the meantime I will have to get my info from the experts on this board. I want to use a name I have in a cell in a msgbox routine.

Currently I have

## Creating Variables Based On Cell Location

Oct 10, 2009

if Row i Column j is activated then a corresponding variable Aij, Bij, Cij or Dij would be created (if not already existing), or cumulated by 1. The problem as I see it - completely generalised - is to create a variable where the variable name itself is formulaic, as well as its value. I know SAS would have no problem with this due to the order in which it parses data steps, but I'm pretty amateur with other programming languages so I don't know if this is blindingly obvious or downright impossible!

## Formula To Get Cell Value Based On Other Cells

May 29, 2014

I have a range of data A1:G52, I need to put a formula in B60 that will find the value in the column H of the range.

To find the right cell in column H, I need to find the row where

B55 matches in column A1:A52
B56 matches in column C1:C52

There will only be on row where they both match, in the row i need the value of the cell in column H

## Populate A Cell Based On Conditions Of Other Cells W/out Putting A Formula In The Cell

Oct 10, 2009

Is there a way to make a cell populate certain text based on conditions of other cells without putting the formula in the cell you want to populate. So that someone could type other text into the cell if the conditions were not met?

## VBA Iterate Through Variables In Cell Formula

Dec 19, 2013

I have a spread sheet that has various references

on the same sheet
on a different sheet ( in same workbook )
on a different sheet ( in a different workbook )

If I click on a example cell it might say

Code:
=5*C4*\$D\$1-Sheet1!C9

Is there a way for excel to be able to iterate through these separate variables ? and know if the reference is on the same sheet ?

Say I wanted to evaluate the references on other sheets ( replacing the link with the value )

So if Sheet1!C9 contained the value 10 the formula would be replaced with

Code:
=5*C4*\$D\$1-10

## Set A Range Based On Active Cell

Jul 16, 2009

I would like to start off in col A, do an offset until a certain value is reached:

## Macro To Add Nested Formula To Cell With Variables

Jun 20, 2008

I want the range in the VLOOKUP below to display its actual value - ie "'6 June 08'!\$A:\$C" in the formula rather than the actual variable name 'strResult'.

Sub PreviousCount()
strDate = "6 June 08"
strColumnRange = "\$A:\$C"
strResult = "='" & strDate & "'!" & strColumnRange

Dim i As Long
i = Range("A2"). CurrentRegion.Rows.Count
Range("D2:D" & i).FormulaR1C1 = "=IF(RC[-3]="""", ""Column A blank!"", IF(ISNA(VLOOKUP(RC[-3],strResult,3,0)), ""NEW INSTALL"", VLOOKUP(RC[-3],strResult,3,0)))"
End Sub

## Sumifs With Condition Active Based On Cell Value?

Dec 20, 2012

I am trying to get a Sumifs to work where one condition is activated based on a cell value.

SUMIFS(\$E\$2:\$E\$6,\$A\$2:\$A\$6,"=Region",\$B\$2:\$B\$6,"="&B1). I have a drop down in cell B1 that lists all the products.

I want the condition to be active only when I make a selection in the drop down, if I don't then I want the Sumifs to ignore the second condition and do a Sum on Region.

## Find Column Based On Active Cell

May 16, 2013

How to find the column based on my active cell...if my active cell is in A1 then it pops up saying your in column A.

## Merge Range Based On Active Cell Row

Jul 27, 2007

I am working on a macros that creates a new row for every data entry. Below is the macros that I have. In the new row, I want for the cells in columns F through O to merge right after creating the row. How do I go about this?

If Sigma = 0 Then

Selection.EntireRow.Insert ' New row for new entries
ActiveCell.Value = "NONE"
ActiveCell.Offset(1, 0).Select
End If

## VBA Insert Formula Onto Active Cell?

Feb 25, 2014

I am trying to use the code below to enter formulas into two different cells.

The first code "ActiveCell.FormulaR1C1 = "code" is running ok.

But the second one "ActiveCell.FormulaR1C1 = "=IF(IFERROR(IF(AND(1*LEFT(D2,2)>=61,1*LEFT(D2,2)<=65),VLOOKUP(AA2,'abc'!A:C,3,FALSE),""),"")="",D2,D2&AA2)" is getting error.

Sub Combine()
Dim wbk1 As Workbook, wbk2 As Workbook

fileStr = Application.GetOpenFilename()

[Code].....

## Active Cell Referred By A Formula

Sep 19, 2006

I have a "Match" formula in a cell that gives me the Row number of the Cell matching the criteria (lets say row 502) and the Column is always B. With VBA I want to make my ActiveCell the cell (B502) referred by the "MATCH" Formula.

## Select A Range Based On Active Cell Location?

Oct 27, 2009

I'm trying to select a range of cells whereby the range is dependent on the currently active cell. I know you can use the "Activesheet.Range("A1:D2").select" method to select a range where the cells are always the same, but I'm after a dynamic selection where the values can be programmatically altered depending on some other result.

For example, let's say that I make a certain cell active (based on the result of some other formula), and I want to select the range of cells in the adjacent column that is X rows deep. Putting this into context, imagine the resultant active cell is B2, I then want to select the range C2:C10, but if the active cell is E10, the range selected would be F10:F18 (if active cell is X, then range would be Y:Z).

The Offset function would allow me to position the cell based on the current active one, but it doesn't let me select a range. The Range function only lets you choose either hard coded or index cells, e.g. "Range(cells(y,z), cells(y,z)).select", but this is still no good because I'd need to know the index value of the active cell (can this be done?).

## Find Text Based On Active Cell Contents

May 11, 2007

I have a workbook with 2 worksheets, A and B. Sheet A contains source data and sheet B a pivot table of this data. I want my user to click on a cell within the pivot table,click a button that runs a macro to find the instance of this value within Sheet A. I did record a macro but it did not work.

## Formula To Find Value Of Cell Based On Selections Made In Two Other Cells?

Feb 2, 2014

I am making a form with drop down boxes and auto fill to make things easier. I have one Box for the Company selection another box for the occupation selection and I need a formula to find a \$ value based on the selections made in these two cells. If I could establish the actual cell reference of the selected data in the second drop down list. The data will be much larger than this example and will live on a separate sheet.

## Macro To Populate Cells With Formula Based On Adjacent Cell?

Mar 31, 2014

I have a spreadsheet with data populated down column B. This size will vary from day to day so is there a macro I can run that will populate Column C with a formula based on their being data in the adjacent cell.

## Auto Fill Down Formula From Active Cell

Mar 11, 2013

I am desperately find a way to fill down formula from active cell, example given below,

AA
BB
CC
Total
QTY1
QTY2

City 1
5
2
3
10
12
15

[Code] .......

I need to fill down col Qty 1 & Qty 2. Number of Rows and Columns vary in my Work Sheet.

My code below, Ctrl+Enter not works.

HTML Code:
Cells.Find(What:="QTY1").Activate
ActiveCell.Offset(1, 0).Select

ActiveCell.FormulaR1C1 = "=RC[-1]+RC[-3]"
'Fill Down Active Column

Cells.Find(What:="QTY2").ActivateActiveCell.Offset(1, 0).Select
ActiveCell.FormulaR1C1 = "=+RC[-5]*RC[-3]"
'Fill Down Active Column

End Sub