# Range Object/property: Calculates 2nd Column Based On 3rd Column's Number

Jun 1, 2009

I'm trying to write a code that calculates 2nd column based on 3rd column's number or vice versa based on the condition set on the 1st column. Below, there are two procedures. "SimpleCalc" and "SimpleCalc2". I first wrote SimpleCalc2, but it isn't working, so I worked around the error by writing SimpleCalc, which selects a cell and moves around by offset. I personally find it hard to read and error prone as I develop more logic around it.

I'm trying to develop more function based on this code, so I need to make it neat and flexible. what I'm doing wrong in SimpleCalc2? Or do you have any suggestion to improve the code "SimpleCalc"? I'm using Excel 2003.

Sub SimpleCalc()

Dim SimpleMethodRng, SimpleMethod As String, i As Integer

ActiveWorkbook.Worksheets("Dashboard").Range("P5").Select

SimpleMethodRng = ActiveWorkbook.Worksheets("Dashboard").Range("N5:P12")

For i = 1 To 8

SimpleMethod = SimpleMethodRng(i, 1).........

## Formula That Calculates AVG Number Based On Exact Criteria

Jan 14, 2014

Is there any formula that could calculate total average number of recruitment days (i'ts one number), but only for "Diana" and "Skipper" (excluding "Jennifer")?

Recruitment daysResponsible
12Diana
13Diana
19Diana
16Skipper
23Jennifer

[Code] .....

I guess it has something to do with AVG and IF formulas.

## Select A Range That Will Be Changing By Column: Method 'Range' Of Object '_worksheet' Failed

Jan 6, 2010

I'm trying to select a range that will be changing by column. I'm not sure why my syntax isn't working. What I've got:

## Copy And Paste Range Of Cells Based On Number Of Cell Values In Column A

Mar 17, 2014

Please see attached sample worksheet. Column A will be generated by the user manually.

I'm looking for a way in VBA to have A1:D20 in Sheet2 copied and pasted in the "Bank Reconciliation" Sheet based on how many "Markets" there are in Column A. Then, once that's complete to have A22:D30 (the smaller box in Sheet2) copied and pasted directly below those results.

I have what the macro would hopefully generate to the right in "Bank Reconciliation" (B6:E54) as an example. So if there's a market in A1, copy and paste the box to B6. If there's a market in A2, copy and paste the box directly below the first (B26) etc. etc. until it's done, then paste the smaller box directly below whatever the macro generates.

Book2.xlsx‎

## Return Column Header Based On Column Criteria And Number Value

Feb 7, 2014

I have the following sample data set and I'm trying to return the appropriate column header based on criteria (i.e. DDD) and a number value which will be somewhere within the range of the table. In example below, the value returned should be Header 2 because the value is greater than those in Header 1 column (range H9 to L26).

Here's data table:

ZZZ5.0015.0050.00130.00
ZZ5.0015.0050.00130.00
Z5.0015.0050.00130.00
YYY5.0015.0050.00130.00
YY5.0015.0050.00130.00
Y5.0015.0050.00130.00
DDD5.0015.0050.00130.00
DD5.0015.0025.0075.00
D5.0015.0020.0065.00
RRR5.0015.0015.0045.00
RR2.5010.0010.0030.00
R1.503.0010.0025.00
UUU0.751.505.0020.00
UU0.751.505.0020.00
U0.751.505.0020.00
P0.751.505.0020.00
T0.100.105.0018.00

CriteriaNumberValue

## Return The Column Number Of First Column In A User Inputted Range?

Feb 19, 2009

I'm creating a macro that creates a co-ocurrence matrix from variables that are adjacent to each other.

In order to proceed, I need to know how to return the column number of the first (leftmost) column in a range that the user selects.

## How To SET Column Range Of Sheet Using Column Number

Jun 16, 2012

I have to SET sh1, then activate sh1 before I can SET rng1. Is there a way to just...

Set rng1 = sh1.Range(Columns(1), Columns(11)) ' why doesn't this work?
Sub Raw_Sales()
Dim sh1 As Worksheet
Dim sh2 As Worksheet
Dim rng1 As Range

[Code]...

## Autofit Column Range By Column Number

Dec 17, 2012

I've been trying various syntax for an hour now and I cannot figure out how to reference a range of complete columns by column number.

I know this works

Code:
Worksheets(strWS).Columns("B:I").EntireColumn.AutoFit

But the number of columns is dynamic, so I don't know which one is the end. So I have the column number but cannot figure out how to reference the column range by number. I have tried versions of the code below but they all give different errors. This one is Run-time error 1004 Application-defined or object-defined error.

Code:
Worksheets(strWS).Columns(Cells(1, 2), Cells(1, lngLastCol)).EntireColumn.AutoFit

I am pretty sure I am close but the referencing is the issue. I can find all sorts of referencing by letter but not number. I've tried

Code:
Worksheets(strWS).Columns(Columns(2), Columns(lngLastCol)).EntireColumn.AutoFit
but I get the same error.

## If (Column A) Within Date Range Then Add Number In Column C

Oct 22, 2009

I'm still confused regarding a complex formula - rather n00b. Using Office 2007 with XP. Can't figure out with help or previous questions.

The following is what I would like this formula to accomplish. Picture included below.

Take all the values of column of C and add them together if and only if the corresponding date value in column A is during the month of January (and February, March, and so forth).

Event DateNameTotal Amount2/21/2009\$4,5002/22/2009\$04/3/2009\$04/25/2009\$9,0004/30/2009\$8,3005/2/2009\$9,0005/7/2009\$5,5005/9/2009\$9,0005/12/2009\$3,000

## Copy Range Column Based On Other Column

Aug 7, 2009

in column B of sheet1 i have every 3rd-4th row a name. i need to copy the name and the the data on the row below the name to sheet2,
the list of names can be min 2 max 30.

so sheet 1 has row 14- name in column B and C. in row 15 in columns d:z data for the name.

i need to copy this to one row in sheet 2 copied for each name up to 30.

## Error "Object Does Not Support This Property Or Method." When Changing The Range Of A Series On A Preexisting Graph

Nov 19, 2009

I'm trying to write some code to make an existing chart display the correct series of data. However, on the ".SeriesCollection..." lines, it gives me the error "Object does not support this property or method." I'm using a Bar-Line graph, if it makes any difference.

## Could Not Get Column Property - Invalid Argument Error?

Feb 4, 2014

I have got a UserForm which pulls data from Excel sheet and show it on UserForm.

UserForm1 has One ListBox name "ListBox1" and Thirteen TextBox's.

TextBoxBatch...... Showing Batch number
TextBox1 ........... Showing Year 1 data
TextBox2.............Showing Year 2 data
TextBox3...............Showing Year 3 data
.
.
.
.
TextBox12...........Showing Year 12 data

It shows data correctly in UserForm1 for TextBoxBatch and TextBox1 to TextBox9 but when it reaches TextBox10 it give error "Could not get Column property, Invalid argument"

And it does not show data in TextBox10 (Year 10), TextBox11 (Year 11), and TextBox12 (Year 12).

I have attached workbook and userform.

Book1.xlsm‎

## Column Number From Range Name

Dec 9, 2009

I have named ranges in my excel sheet.I wish to however find out the column number from each of the names and use it as a variable for later calculations.How do I do this?

## Does A Property Exist To Return The Letter Reference For A Column

Jan 24, 2008

I need to build a formular in a cell that uses various columns in it's row such as: =A2+A4+A17+A24. In the past I've used simple number references to the columns (a is 1, b is 2,.....) I can't do tht though if I'm using a macro to automate the creation of a formula, right?

Is there a property I'm missing that will return the letter reference to the column instead of just the number as Activecell.column does?

My alternative is to write my own code to do this, but you know the bit about not reinventing the wheel....

## Use Two Different Calculations Based On ID Number In Column

Jun 28, 2012

I have a list in Excel with several hundred 8 digit ID numbers in column D. Column C contains the total number of hours the student employee worked. Column E needs to calculate using two different equations depending on if the ID number in column D is a manager's. If the ID number is an employee the total hours worked is divided by 8. IF the ID number represents a manager the total hours worked is divided by 4. I've attempted this by using an array for the manager ID list and also using VLookup without success.

In essence I need to loop through column D, check which type of ID number is present, and then enter the correct formula in column E.

## Concatenate Based On Column Number

Jun 6, 2007

I need to cocatenate anything between and including column 4 and col (unkown until macro is run)

is there a way to do this?

Basically need to add all the cells from 4th column to whatever col is. Example if col = 12

then I need columns 4 to 11 all rows together

## How Can I Check A 4 Column Range For A Particular Number

Jan 11, 2009

I would like to check a 4 column range to see if a particular number has been added to a range of number.

this is the formula that I wrote but it does not test beyond the 2nd column.

=IF(OR((MATCH(3,COLUMN_D)),(MATCH(3,column_c)),(MATCH(3,column_b)),(MATCH(3,Column_a))),3,"")

Does anyone have an different way of check for this?

## Error 438: Object Doesn't Support This Property Or Method

Oct 3, 2008

When I run my code (which opens Excel from Access) it does everything it is supposed to but I then get the above error but I do not get the error if I open the VBA window and then run the code. (The code is password protected, not sure if this is relevenat though).

## Run Time Error 438: Object Does Not Support This Property Or Method

Jan 8, 2009

I'm getting a really weird error in that I can't reproduce it. It sometimes occurs when I open the document and sometimes not. HEre is the
Private Sub Workbook_Open()
Dim dic, Dn As Range, rng As Range

Set rng = Sheets("Moisture").Range("B2:B1000")

Set dic = CreateObject("Scripting.Dictionary")
With dic
For Each Dn In rng
If Not .exists(Dn.Value) Then

End If
Next Dn
Set wksPayment = Sheet10
wksPayment.ListBox2.List = .keys
End With
End Sub

The line that hangs up is the wksPayment.Listbox2.List = .keys line. Any ideas or ways to test or further figure out what is causing the problem?

## VBA - Cells Property Failing - Object Does Not Support This Method

Jan 5, 2010

I am trying to use the Cells Property in VBA but it is not working and I cannot figure out why for the life of me. Below is my ....

## VBA To Get Average Of Range Of Cells Based On Named Range In Different Column

Apr 10, 2013

I am trying to calculate some averages. What I have is 3 columns of data in A, B, C, also the "tasks" in A are in named ranges ex: "Award Contract" is a named range - "Task_Award" and "Confirm Updates" is a named range - "Task_Updates". I've attached a sample excel sheet.

I'd like to be able to create a macro to evaluate column A, and for every row in range "Task_Award", give me the average of the corresponding cells in column C and put it in the same range of cells in column B , then, for every row in "Task_Confirm" then give me the average of the same range of cells in column C and place the result in the same range of cells in column B. This is my very first post so I hope I am doing this correctly. I have 77 of these task ranges to evaluate and it will take a long time to do it manually. I'm thinking of a loop function.

## Autofilter Column Based On Month Number?

Dec 8, 2013

I am using the following to autofilter a column based on a month number

Worksheets("Data").Range("\$A:\$M").AutoFilter Field:=8, Criteria1:=Month(tempmon)

tempmon contains the month number (e.g 9 for september)

when I run this it filters but tempmon is equaling 1 when I know it is set to 9

i have tried

Worksheets("Data").Range("\$A:\$M").AutoFilter Field:=8, Criteria1:=Month(9)

## Copying Cell Based On Number In Other Column

Aug 13, 2009

I have a name in cell A1 and a number of 20 in cell B1. Is there a function to copy the name in cell A1 the number in cell B1 (20 in this case) times into column C?

## Calculate Number Of Dates Within A Column Based On Month

May 15, 2009

I have a column say column B for example that has a list of dates in the format dd/mm/yyyy. I would like a summary at the top of the columns to state how many dates there are for the current month. But I wondered if this was possible based on the TODAY() function or similar. Thus the user would not have to change anything.

So for example at the start of the month it may state 14. Half way through the month down to 6 and at the end of the month 0 for example.

## Copy Formulas Based On The Number Of Rows In Column

Jun 16, 2009

I have formulas in cells B8:F8. I need to copy these formulas down based on the number of rows in column A that has data. The data in column A will vary based on user input.

## Count Number Occurrences Based On Criteria Column

Feb 5, 2013

I get this work a lot and am looking for a much more effective way of working the dataset,

I have numbers like 1.1, 1.2, 1.3 and so on.......
These come under criteria like (1) = 1.1, 1.2, 1.3 and so on.....

I am look for an automated way of doing a count of any number that falls under this criteria, so I want to count based on criteria (1) it would count all 1.1,1.2,1.3 and so on as one count.

I am attaching a sample document to see how it is laid out. [URL] .....

## VBA To Highlight Cells Downward Based On Column A Number

Aug 26, 2013

I would like to highlight the cells in Column A the have numbers in them. Starting with that number shift down based on the number that many rows and highlight them.

HTMLSheet1 *AB154020842*4020843*4020844*4020845*4020846*4020847*40208483
5010909*50109010*50109011*50109012*50109013*50109014*501090153
50112016*50112017*50112018*50112019*50112020*50112021*501120222
50206523*50206524*50206525*50206526*50206527*50206528*50206529450212430*50212431*50212432*502124

## Vlookup (search All Of Column A For The Highest Number In The Range)

Sep 4, 2009

I WANT it to search all of column A for the highest number in the range (which will change daily to a new high number above the highest currently), and return the value in column 4 (column D) that corresponds to that value from column A. This is what I have, but it is not working, it is giving me the number (1) in Cell A800 (which is the lowest valued number in my range. The numbers range in value from 1 to 762 with none repeating (they are sequential). The formula I have is :
=+VLOOKUP(MAX(A76:A2000),A76:I2000,4)

## Convert Column Number To Letter From A Range Objec

Jul 7, 2009

Is there an easy way (without writing my own conversion function) to get the column letter from a range object, as opposed to the column number?

## Multiply Column Of Values By A Random Number Within Range?

Apr 20, 2012

I have a column of values I need to multiply by a random number (percentage) within a range of numbers (15%-22%).

How can this be done?