Loop Column & Display Variable In Other Column

Dec 6, 2006

on the syntax of things in VBA. here's a description of what i'm trying to do:

1) loop through a particular column ("N"), and compare the values (if not blank) with corresponding cells in another column ("V"). i.e. - comparing N1 to V1, N2 to V2, etc...

2) run If, ElseIf statements to display appropriate values in different column ("O"), but on the row that corresponds to the row the values in Step 1 are compared. i.e. - if N1 > V1 then cell O1 = value; if N2 > V2 then cell O2 = value; etc...

here's an example (but obviously not in proper syntax). hopefully someone can help me convert it to Excel VBA:

Function CalculateFR()

Dim Col1 As Column = ColumnN
Dim Col2 As Column = ColumnV
Dim Col3 As Column = ColumnO
Dim Cell As Cell
Dim IndexValue As Integer

For Each Cell In Col1
If Col1 > Col 2 Then
IndexValue = 5
Else If Col1 < Col2 Then
IndexValue = 4
End If
Next Cell

If Not IsEmpty(Cell) Then Col3 = IndexValue

End Sub

View 7 Replies


ADVERTISEMENT

Excel 2013 :: Formula To Subtract 7th Column Values From Column Before 1st Loop?

Feb 21, 2014

I have a code that inserts a new column after every 7th column. I want to include a formula where every 7th column value is subtracted from the values present in the column before the 1st, or you can think of it as subtracting 7th column of the present group from the 7th column of the previous group. Example: The range of my data starts from col F, then

F (7th) New Column (G) H (1st) I (2nd) J (3rd) K (4th) L (5th) M (6th) N (7th) New Column (O) P (1st) Q (2nd) R (3rd)

So, New Column (O) = N - F
and the next New Column (W) = V-N ...

NOTE: Column G can be ignored.

I want to add a looping function to this so that it will continue to subtract for the other respective columns as well. How do I incorporate this into the following code?

[Code].....

I'm using Excel 2013.

View 2 Replies View Related

Formula To Display Column Header As Result When Any Value Exists In Cells Under Column

Jul 25, 2014

I am trying to write a formula where the column header of the row in which a value other than 0 exists, will display for each instance (row) where a value exists in an array spanning 3 columns. So the result cell could be any of the three column headers, or a combination thereof.

I started the formula in P2 of the GL Detail-2012 tab. File attached.

Here is what I started: =INDEX($M$1:$O$1,SUMPRODUCT(COUNTIF($M$2:$O$67756,))). Not working.

View 7 Replies View Related

Display Column Header If Any Value Other Than 0 Exists In Multi-Column Array

Jul 22, 2014

I would like to display the column header of the row in which a value other than 0 exists in a cell for each instance that value exists in an array spanning 3 columns. So the result cell could be any of the three column headers.

I started the formula in P2 of the GL Detail-2012 tab. (FILE TOO BIG)

Excerpt:

Resin Sand Eliminated Segment
- (12,896.65)-
- (6,570.85) -
- (11,503.80) -
- (13,188.50) -

If there is any value other than 0 in Resin, Sand or Eliminated columns, return that column's header under Segment.

Here is what I started: =INDEX($M$1:$O$1,SUMPRODUCT(COUNTIF($M$2:$O$67756,)))

View 2 Replies View Related

Check One Column, Get Value From A Second Column And Display Sum In 3rd Column

May 27, 2007

I am tying to total all the sales for a given customer. This is what I have at present

Customer Sales
CustA 1000
CustB 500
CustA 1250
CustC 750

Elsewhere in the spreadsheet I have the following

Customer Total Sales
CustA
CustB
CustC

As you can probably see I need to get the sum of each customers sales and display this under Total Sales. I thought about using VBA but am not really sure where to start.

If anyone has any ideas how I could progress this I would be most grateful. I am using excel 2007

View 9 Replies View Related

Concatenate Column With Adjacent Column In Loop

Sep 27, 2007

I had a problem finding something then deleting the column, I tried to alter the code from that thread to solve a new problem and I am close (I think), but not quite there. I need to find the column "Group" and merge it with the column "Sex" which is offset by 1 column. This code will go through and find the column "Group" and merge for the first cell, but will then get stuck in a loop just concatenating "Sex" to the end continuously. I need it to concatenate both columns entirely together. Any simple fix to what I modified? Previous thread should you be interested:Find Part Text On All Sheets & Delete The Column.

View 9 Replies View Related

Lookup (display The Value In Column B If Column C Is Not Blank)

Oct 31, 2009

What i am looking to do is have column D display the value in column B...if column C is not blank. Here is an example.

View 4 Replies View Related

Cell B1 To Display True If Any Of The Values In Column C Match Otherwise I Want To Display FalseOR Function Confusion

Dec 16, 2008

Example:....

i m wanting Cell B1 to display true if any of the values in column C match otherwise I want to display false.

A 1
B 0
C 0
D 0

But I can't determine how to get this done.

View 5 Replies View Related

Assigning A Variable And Pasting Variable To Last Unused Column

Nov 19, 2008

to assign a variable to equal a Constant variable, then I need to find the last unused row on the worksheet, then paste that variable down the column (1-12200 or so rows). I also need to assign Strings for the first two Rows in the target column.

View 14 Replies View Related

Autofill With Range That Is Column Variable And Row Variable

Apr 4, 2008

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 Related

How To Loop Through A Column

Dec 7, 2009

I have a worksheet and it has some information on it (ill show an example in a min). will all the information i need is in one column. there is only the name of the item that i need the rest can be deleted. like so ........

View 12 Replies View Related

Column - Row Loop

Sep 29, 2008

I am trying to loop through columns and rows to get the sum of each and delete that column that equals zero. I have-

Dim lastrow
Dim r, c As Integer
Dim RowSum
RowSum = 0

ActiveSheet.UsedRange.Select
lastrow = Selection.Rows.Count

For r = 2 To 80
For c = 2 To lastrow
RowSum = RowSum + ActiveSheet.Cells(r, c).Value ' here I get error Application-defined or object-defined error

Next c
If RowSum = 0 Then
ActiveSheet.Cells(r, c).EntireColumn.Delete
End If
Next r

What am I doing wrong?

View 9 Replies View Related

Loop Through Column

Nov 2, 2009

The below code is supposed to loop through column a and create a copy of a worksheet change the name of the worksheet to the value found in column A

I need change the loop so it starts at A8 and Ends at A59. I have no Idea how to do this.

Sub AddSheets_FromList()
Application.ScreenUpdating = False
'assumes starting macro on first value in date list
Dim ListCount As Integer
Dim NewSheet As String, LastSheet As String

LastSheet = "Weekly Results"
ListCount = 0

View 9 Replies View Related

Loop Through Each Column Subtracting Until 0?

Apr 17, 2014

im looking for a macro that will take away the free stock value from the values in the following columns until the value reaches 0 then subtract from the next column with a value in it until there is no more left in the free stock to subtract. if the value reaches 0 it can be blank.

so for example as shown in the image attached cell b2 shows 150 in free stock, then the next column with a value in is d2 which shows 100 so takeaway until this reaches 0 which leaves 50 and this can be shown as blank now, then move to the next column which is e2 and this shows 30 so this can now be blank also, finally f2 shows 50 so minus the remainder from the original 150 which 20 and this should now be 30.

my data is in a pivot table so a paste special to another sheet may also be necessary. The amount of columns could also vary due it being a pivot table.

View 5 Replies View Related

Selecting Column To Loop Through

Jan 26, 2009

I have created a macro that loops through column 4 and gives the value of 2 other cells acording to what is found.

The problem comes when the macro only works sucecefully if i click on a cell in that column. Therefore i think the macro is not doing this.

Im sure that i have used the right code does anyone know what is wrong.

View 12 Replies View Related

Loop Through Column Values?

Feb 5, 2009

What the easiest way of looping though the values in a column in a macro? How to you "call" the cells in the macro?

View 4 Replies View Related

For Loop (check The Value In Column)

Mar 18, 2009

how to write a for loop for this coding.
considering x as row,
i want to check the value in column1 for n rows.

View 7 Replies View Related

VBA - Trying To Loop Code Until Last Row Of Column

Feb 24, 2012

Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Dim rw As Integer
Dim LastRow As Integer
LastRow = Sheet8.Range("B16:B300").End(xlUp).Row
For rw = 16 To LastRow

[Code] ........

Cannot get the loop to run. I am trying to make it so the loop will stop when it reaches the last non empty cell.

View 6 Replies View Related

Loop Through Each Column And Colour Red If Below X

Aug 29, 2012

I'm trying to build code to loop through each column of data and colour the cell red if it is below x.

Problem is each column has a dynamic number of rows, it changes, and I have a dynamic number of columns as well.

I currently have code to do it on 1 row, but am trying to loop through all filled columns with no success, heres the code:

Code:
mycol = Cells.Find("", SearchOrder:=xlByColumns, _ LookIn:=xlValues, SearchDirection:=xlPrevious).Column
MsgBox mycol

[Code]....

View 8 Replies View Related

VBA Column Increment Loop?

Apr 15, 2013

I have a number of strings in adjacent columns in the same row, like so.

Coulmn1 Column2 "blank" Column3

If the cell is blank I want to hide the respective column .

I'm guessing the code will roughly take the form of the code below with an if statement nested in a for statement.

Sub (test)
With Range("BD22")
For i = 1 To 10
if range (i,22) = "" then hide
else don't hide
.Offset(1, 0).Formula
Next i
End With

View 1 Replies View Related

Loop Down Column & Across Rows

Aug 17, 2007

I need to create a loop within a loop. First loop works fine as follows:
-------------------------------

x = 0
Dim DirArray(0 To 80) As String
Dim intI As Integer
For intI = 0 To 80
DirArray(intI) = Range("start").Offset(intI, 0).Value
Next

For x = 0 To 80
lob = DirArray(x)
Range("lob") = lob
Calculate
Sheets("WBB LOB Summary").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Next

----------------------------
At the 'start' point, first loop works downward through rows. Second loop would work across columns in each row within the first loop. I will have to use a variable for 'y' (the across value) b/c the number of cells containing data can vary between 3 & 9, but I have a COUNTA in each row setup to use as the variable.

So spreadsheet data would look like this with A being the 'start' cell:
A 1 2 3
B 1 2 3 4 5 6
C 1 2 3 4

I need to pickoff A1, A2, A3, B1, etc, picking up every sub-category on the sheet.

View 6 Replies View Related

Display Last Number In A Column

Nov 5, 2008

I have a column that is updated on a regular basis and I want the last entry to be displayed in a specific cell.

View 3 Replies View Related

Display Just Numbers In A Column.

Nov 19, 2008

I have a column with some cells containing just numbers, some cells containing numbers and letters and some cells containing just letters. I would like to know how to display just the cells that contain numbers only, without deleting any of the other cells.

View 9 Replies View Related

Sum A Column And Then Display The Result

Jan 15, 2010

I want to sum a column and then display the result but in the same cell put some text:-.....

View 4 Replies View Related

Display Column Name In Reference?

Apr 12, 2013

I'd like to know if there's a formula concatenation I could use that, when I reference one of a columns cells elsewhere on a sheet, will tell me the name of the column it came from if I assign that column a name. I don't know anything about VBA but if that's the only way I'll try to figure it out. Excel 2013

If a8 said 55 under a column called Qtr 4 then a reference to A8 would show 55 (Qtr4)

I could always simply use an absolute reference for the beginning of the column but if there's a faster way where I could do these on the fly I'd prefer that

View 3 Replies View Related

Display Bottom Row Of Column

Nov 21, 2013

If a sheet contains 200 rows of data, is it possible to make the last ten rows of data be shown when the sheet is opened.

View 6 Replies View Related

Display The Last Entry In A Column

Jan 3, 2009

Looking for a formula that will only display the last entry in a column.

ex: Column A has data that is sporatic. (mike, "blank", "blank", fred, mary, "blank" ,john. I want to display in C1 the last entry, "john")

View 9 Replies View Related

Display MsgBox If Sum Of Column Is Zero

Jul 8, 2006

I have a file with 2 tabs that linked 1) Input 2) Spread. The idea is for the user to spread the total number they keyed in the "input" by months. I need a code that will display a msgbox if the sum of the variance column in Spread <> 0, so it can prompt the users that they still have to do the spreads before closing the files.
I want the action to happen when they attempt to save or close, just to remind them it's not done yet.

View 7 Replies View Related

Display Last Column Value In TextBox

Aug 18, 2007

Is there any wayt to set the ControlSource of a textbox on a form to display the largest value within column A?

View 4 Replies View Related

For Loop And Delete Entire Column

Oct 1, 2012

I have two worksheet. One worksheet (ws1) contains a list of item I want. The other sheet (ws2) contains multiple columns where the header (row 6) is named by item name.

I have the following code which deletes the entire column if the header name is not in the list contained in ws1 :

VB:
Sub delete_col()
Dim wanted As Boolean
Set ws1 = Workbooks("test1").Sheets("aaa")

[Code]....

First of all, this loop does not work properly since deleting the entire column shift them on the left, so when I first analyze column 11, if I delete it and then analyze column 12, the real column 12 now became column 11 and so on...

Secondly, this code is pretty slow. I am pretty sure I don't have to loop through my initial item list everytime I do Instr on a new column.

View 9 Replies View Related







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