Vary Columns Based On A Number

Feb 5, 2009

You can always add numbers together. however, how can you vary columns based on a number?

for example, i can always do things like the following to change the number of rows selected in a column based on the variable numRows.

View 2 Replies


ADVERTISEMENT

Format A Range Of Cells, Columns And Rows Will Vary In Length

Jun 16, 2009

I created a macro on an Excel spreadsheet:

Sub Macro1()

Range("C11:D19").Select
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
Range("E5:E20").Select
Selection.NumberFormat = "0.00%"
Range("D24:E43").Select
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
ActiveWindow.SmallScroll Down:=24
Range("D58:E339").Select
Selection.NumberFormat = "$#,##0.00_);($#,##0.00)"
ActiveWindow.SmallScroll Down:=57

End Sub

When the macro is run, it works with the exact amount of data that I've entered.

However, because I have specified a specific range, if I add a row or column of data to the spreadsheet and run the macro again, it throws the whole thing off (certain cells get formatted when they shouldn't and others aren't formatted)

How do I tell the macro to look in the spreadsheet for varying ranges of data and format those cells?

View 9 Replies View Related

Excel 2007 :: Macro - Copying Column In Which Number Of Rows Can Vary

Sep 23, 2013

I am using MS Excel 2007 and I am trying to create a macro which will copy column E and paste the data in column E to the bottom of column D. However each week the amount of rows in these column with vary, they will always be the same amount of rows in column E as in column D but there may be 20 rows one week and a 100 rows the next.

The formula I currently have is below but this will only work for a specified number of rows. How I could change this to work for any number of rows?

Code:
Sub IPT()
'
' IPT Macro
'
'
Range("E1").Select

[Code] .....

View 2 Replies View Related

Dynamic Sum Function (number Of Sheets Can Vary From Time To Time)

Sep 8, 2009

I need a sum function in A1 of a "Total"-sheet that totals cell A1 in every sheet with a certain color on the tab. The number of sheets can vary from time to time.

Any idea about a dynamic sum function that will do this, in combination with VBA?

View 2 Replies View Related

Loop Based On Number Of Columns

Jan 10, 2014

I have a table of data (lets say A2:H30) i want to populate so i want the macro to loop based on how many columns i have? I am using this to post the data based on # of rows..

Sheets("Main Cal").Select Dim bottomA As Integer
bottomA = Range("D" & Rows.Count).End(xlUp).Row
Dim c As Range
Dim ws As Worksheet
For Each c In Range("D3:D" & bottomA)
Set ws = Nothing
On Error Resume Next
Set ws = Worksheets(c.Value)
On Error GoTo 0
If ws Is Nothing Then

View 1 Replies View Related

Distribute Across Columns Based On Number

Dec 20, 2006

I have a list of parts with quantities that i would like to distribute among several columns that represent cities, based on the number of parts in stock. For Example, if i have 5 parts and ten cities, i want it to put one part in the first five. In the case where theres more parts than cities i want it to loop back to the first city and add one and so on. I attached a sample spreadsheet.

View 5 Replies View Related

Combine Two Columns Based On Name / Number And Score

Feb 19, 2014

I have three columns in the spreadsheet.

sheet1

A BC
Santhosh314
Praveen224
Praveen012
Praveen37
Santhosh38
Praveen120
Praveen036
Santhosh19

In another sheet (sheet2) output come like this.

A B
Praveen 3/7
Santhosh 3/8

View 2 Replies View Related

Capture Values Based On Number Of Columns?

Jun 7, 2012

My data comes from 3 different sources

Source A = 4 columns (3rd one is important)
Source B = 8 columns (3rd one is important)
Source C = 5 columns (4th one is important)

Is there a slick way to capture values based on the number of columns in that row?

View 1 Replies View Related

Insert Rows Based On Number Of Columns Used

Jan 31, 2008

I have a spreadsheet which contains data needing to be re-formatted into a suitable format for my database. So far I have been doing this by hand, but want to make it quicker as I have over 2000 lines to sort out. I am trying to write a macro that determines whether there is data in the column next to the active one (D2), and if there is counts how many columns there is data in. Once it has done this it needs to insert the same number of rows as there are columns, then copy the column data and transpose it into the empty rows. My macro seems to work at first, but it exits after around the 4th row, but I can't work out why.

Sub split_For_Database()
Dim No_Of_Cells As Integer
Dim Start_Cell As Range
Dim Cell As Range
Dim LastRow As Range
Dim i As Integer
Dim lRows As Long
Set LastRow = Range("C65536").End(xlUp)
i = 0
Set Start_Cell = Range("C2")
Set Cell = Start_Cell
Do While Start_Cell.Address <= LastRow.Address
Do While Cell.Offset(0, 1) > 0
i = i + 1
Set Cell = Cell.Offset(0, 1)
Loop...................................

View 8 Replies View Related

Trying To Count Number Of Occurrences Based On Content Of Two Other Columns

Jan 30, 2014

I am working on a spreadsheet that will provide count of types of complaints for particular areas over a running time span. I have tried a multitude of formulas but not sure how to write any of them correctly. What I am trying to do is generate a count of area type by whether it is formal or informal. (i.e. I want to know if there are x formal finish issues vs. y informal finish issues and so on.) This information will get charted and be kept "real-time" user input.

Type
Description

Concern

Formal
Informal

[Code] ....

View 6 Replies View Related

Hide/unhide Number Of Columns Based On Cell Value

Jul 17, 2007

If I have a value in A1 and run a macro [button], I want to have it hide a number of rows.

Each number is one column.

eg if A1 = 2 then B:C are visible, D:IV Hidden
if A1 = 3 then B:D are visible, E:IV Hidden
if A1 = 4 then B:E are visible, F:IV Hidden

up to 200 columns.

I tried it as a select case, but it is limited to just 22 cases which obviously not enough.

View 9 Replies View Related

Rearrange (sort) Columns Based On Number In Column Header String

Apr 3, 2014

I want to rearrange(sort asscending) columns based on numerical value in column header string through VBA macro. Please check attachment.

i.e. (Present Data)
# A B C D
1 col.1 col.4 col.3 col.2

(Output Data )
# A B C D
1 col.1 col.2 col.3 col.4

test.bmp‎

View 2 Replies View Related

Reference Cells Keeping Columns The Same But Change Row Number Based On User Input

Dec 10, 2012

Date
#
Lname
Fname

[Code].....

With that said, I want to put the row number of a user in (I have a few thousand on this spreadsheet, and all the = data be automatically pulled based on the row number I put in cell B1. So I'm hoping to write in cell B1 the following - "143" (without the quotes), and the remaining cells in column B automatically pull that data based on that, so it would look like the following

Row #
143
Name:
=C143&" , "&D143&" "&E143

[Code]....

How do I write the functions to keep the columns the same, but change the row number based on the number I input?

View 3 Replies View Related

Get Text To The Right - Length Will Vary

Feb 8, 2011

If I have a cell with this info: [6126]BOB SMITH

What formula can I use to get BOB SMITH. The length of the name will vary. The number will change, but will always be 4 digits and will have the brackets. [XXXX]

View 8 Replies View Related

Vary Data Marker Size By Value?

Apr 17, 2014

Is it possible to vary the size of data markers (eg spots) in a chart by their value?

View 2 Replies View Related

Vary Source Data Via Combo Box

Feb 23, 2007

I'm trying to amend a chart via combo box. Cell C15 returns a dynamic named range, based on a vlookup.

My problem is in having the code pick up the value that C15 returns, rather than the formula in it.

Set rng = Sheets("Chart lookup").Range("C15")

ActiveChart.SetSourceData Source:=rng, _
PlotBy:=xlColumns

View 7 Replies View Related

Testing For Values That Vary Within Specific Tolerance?

Apr 22, 2014

I'm trying to identify where a set of values 'agree' within a specified tolerance.

I have a list of scores from different sources, and wish to both average them (easy to do) but also to identify how well they 'agree' by flagging those which have a difference above a specified tolerance. The numbers are all integers, and the tolerance will be an integer.

To give an example, I might have cells with values (2,3,2,2) and these agree within a tolerance of 1. If I had cells of (2,4,2,2) these wouldn't agree. I'd like to flag (by the creation of an 'X' within a separate column) where these cells do not agree.

I've done this previously by using chained If and Or statements (e.g. if A1 = D1 or A1 = D1+1 etc etc) but this is laborious and means I have to change logic every time I add a new sample.

Is there a way of specifying a range of cells (e.g. A1,D1,H1, J1) and seeing if the values of these cells agree to within a stated tolerance? I'm happy to define named cells to specify and argument/attribute.

View 2 Replies View Related

How To Make Universal List That Can Vary By Different Location

Mar 12, 2014

I am trying to use HLOOKUP to find a location in another sheet and display all the names in that specific location. Also can change location at will and will display the names for that location.

I have attached a spreadsheet that kinda has what I want. It does not have to be HLOOKUP but that is all I know, Hlookup and vlookup.

View 4 Replies View Related

Opening File Paths Where Filename Will Vary

Feb 4, 2014

I'm trying to write code that will open files within a specific folder. However, the end of each filename is going to vary over time to the point that I'll never be able to predict the exact filename.

Is it possible to write something like:

Code:
Set wb = Workbooks.Open "C:\folderfile_filenumberxxxx.xlsx"

Where the "xxxx" signifies the variable element of the file name that could be any four characters? Failing that, is there a similar possible alternative?

View 1 Replies View Related

Range - Dynamic Use #rows & Cols Vary

May 29, 2007

i have data that will vary from item to item in the $ of rows associated
and the # of columns to be dealt with.

item1 Line 1
Line 2
Here i want to have a formula put and copied across X columns
item2 Line 1
Line 2 other data going across in the columns
Line 3
Line 4
Here i want to have a formula put and copied across X columns
item3 Line 1
item4 Line 1

get my meaning??

columns involved will be known before i get to this bit of code !!
as an example lets say it's 8 columns

using the record macro i get this bit of code

Range("E14:M14").Select
Selection.FormulaR1C1 = "=SUM(R[-10]C:R[-1]C)"
This is fine but i don't know what row or the columns are before i run the macro so the Range
("e14:M14").select wont really work.

I've sent sometime going through MRexcell searching for tips but found nothing also googled for dynamically pasting across unknown columns

this is close to my idea in my mind conceptually
Range("C2:C" & LastRow)
but it doesn't make the columns dynamic eg column 8 being = H and then P = 8th column on and the last with data.

View 9 Replies View Related

Charts: Vary Colors By Point With Area Fill Effect

Oct 30, 2006

I am attempting to create simple charts to show survey results 2005 v 2006. I have a single series in a bar chart - chart type. The wizard initially makes both bars the same fill color. I right-clicked one of the bars to start the Format Data Series Wizard. On the 5th tab, "Options", I ticked "Vary Colors By Point". A new color was assigned to the top or first bar. So far so good. But instead of these colors, I would like to use a different fill effect for each data point in the series

So I walk through the wizard and desiqn a fill effect and click ok. The fill effect is assigned to both data points. I checked where I had ticked "Vary Colors By Point"
It is still ticked. Can I override this somehow so that each data point can have a unique fill effect?

View 3 Replies View Related

Macro For Spreadsheets With Different Number Of Rows And Constant Number Of Columns?

Mar 1, 2014

I work on a daily basis with spreadsheets in excel. The number of columns is the same, but every single spreadsheet has a different number of rows. I recorded this macro in a table with 1196 rows and I would like to use this macro also in other tables with a different number of rows.

Sub City2()
'
' City2 Macro
'

'
Selection.Copy
Cells.Replace What:="POMPANO", Replacement:="Pompano Beach",

[Code].......

View 12 Replies View Related

Excel 2013 :: Set One Columns Text To Color Based On Another Columns Results?

Apr 9, 2014

how to set one entire columns text to two different colors based on another columns values. So for example I have column A and B. Column A has two values called Internal and External. Column B is a title table so the entire column is just titles. We'll say it goes for 20 rows if you need a row count. What I am looking to do change the text in Column B to Red for External and Blue for Internal. I tried the conditional formatting and I just can't seem to find the right option.

I'm using Win 8.1, Office 2013.

View 4 Replies View Related

Seperating NUMBER TEXT NUMBER Into Different Columns

Dec 3, 2008

What If we had to replace any number..
Lets say, if we had to seperate NUMBER TEXT NUMBER in different combinations....
B2 contains values like these then

TOM CRUISE 12
TOM 5879 CRUISE
TOM CRUISE 123456789
123456789 TOM CRUISE
123 TOM CRUISE 456

[ = SUBSTITUTE(B2,"1234567890","") ]

I am at my wit's end pondering over it?

How to make the SUBSTITUTE function work for each individual digit?

View 11 Replies View Related

Enter And Highlight Data In Columns Based On Other Columns

Jul 10, 2009

I have a worksheet with 20+ columns. For this macro, I only need to focus on 4 of them. However, none of these columns are ever in a fixed position so the macro would need to find them by name and NOT by column position. Here they are...

1. Vacation Type (will only have a text value of either "Cold" or "Warm")

2. Vacation Started (will always have a date *x/xx/xxxx)

3. Vacation Ended (sometimes it will have a date '*x/xx/xxxx' and sometimes it will NOT have a date and will be truly blank)

4. Number of Days (currently has ALL truly blank cells)

THIS WHOLE MACRO SHOULD NOT BE CASE SENSITIVE ANYWHERE

Here's what I would like the macro to do...

Scenario 1 - for "Cold" values Find "Cold" text values in the "Vacation Type" column

"Cold" values WITH a date in the "Vacation Ended" columnIF there IS a date in the "Vacation Ended" column in the same row, put the number of days difference between the "Vacation Started" column and "Vacation Ended" column in the "Number of Days" column.

The amount of days in the "Number of Days" column will determine whether these cells should be highlighted GREY or RED.

Scenario 1-AIF the number of days difference is 7 days or less, highlight the cells in the "Vacation Ended" column and "Number of Days" column RED.

OR
Scenario 1-BIF the number of days difference is MORE than 7 days, highlight the cells in the "Vacation Ended" column and "Number of Days" column GREY..................................

View 2 Replies View Related

CopyDown To The Last Row Of Data As This Will Vary From Sheet To Sheet

Nov 9, 2009

I've have a piece of coding.

Sub CopyDown()
Range("A2").Select
ActiveCell.FormulaR1C1 = "=IF(RC[1]=1,R[-1]C+1,R[-1]C)"
Range("A1").Select
ActiveCell.FormulaR1C1 = "20090000"
Range("A2:A200").Select
Selection.FillDown
Range("B2").Select

End Sub

This works fine, but I would like it to look in Column "M" but still insert in Column "A",

Also instead of Range("A2:A200") I would like it to copy down to the last row of data
as this will vary from sheet to sheet.

View 9 Replies View Related

Selecting Worksheets Array When #of Worksheets Will Vary

Apr 26, 2006

How do I modify this macro so that the worksheet array will select all the worksheets except sheet 1?? My workbooks will have varying numbers of worksheets ...

View 3 Replies View Related

Formula To Calculate Based On Start Month Number And Duration Number Of Months

Mar 14, 2014

I need a formula that will calucalte the monthly total based on the following conditions:

Col A = Yearly Cost
Col B = The number of the month when the costs are starting (1=Jan etc)
Col C = The duration or the number of months for which the costs are to spread
Col D is Year 2013 with the months across columns D-O. Row 1 above those columns shows the month's corresponding number.

Right now I have =IF($B3<=D$1,$A3/$C3,0) however if my start month is 1 and my duration is 5, I need the costs to stop after May. I've attached a sample file. Calculate based on start month and duration.xlsx

View 3 Replies View Related

Generate Text Value Based On Number Of Times ID Number Previously Appeared?

Aug 3, 2012

Is there a function that allows you to read column A for an ID (these may or may not include letters/numbers/"?", are non-sequential and are of variable lengths) and, if it is the first time that it has seen an ID column B will read "sample_1_arm_1", if its the second time it has seen an id it will read "sample_2_arm_1", etc? An example of what I am trying to do on a much larger scale:

id
event_name
C83-858
sample_1_arm_1

[Code].....

View 3 Replies View Related

Calculate Number Of Days Between 2 Dates And Then Assign Number Based On Answer

Mar 22, 2014

How to create a spreadsheet with what I think will be a very simple formula?

If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2.
If date in B2 - date in A2 is 7 or less days but more than 1, put a 2 in cell C2.
If date in B2 - date in A2 is 30 or less days but more than 7, put a 3 in cell C2.
If date in B2 - date in A2 is 90 or less days but more than 30, put a 4 in cell C2.
If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.

OR

Another, maybe simpler, way of saying it is:

If date in B2 - date in A2 is 1 or less days, put a 1 in cell C2.
If date in B2 - date in A2 is 2-7 days, put a 2 in cell C2.
If date in B2 - date in A2 is 8-30 days, put a 3 in cell C2.
If date in B2 - date in A2 is 31-90 days, put a 4 in cell C2.
If date in B2 - date in A2 is 91 days or more, put a 5 in cell C2.

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved