Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
pulling values from column g into a variable, and returning variableon spreadsheet
Im running into some issues and I have an idea as to what the problem
is but I have no idea how to solve it. The following code will highlight any rows that have a column F value = RollVal. I tried to modify it to also add up all the values of column G if the value of column F and return this value in cell J2, but my coding skills seem to be severely lacking. I think the problem is that it is trying to include the values of the header row, which is obviously not currency or any numerical value at all. Would making the objRow start with Row 2 fix this? Here is the code: Sub RollCalc() Dim RollVal As Long Dim objRow As Object Dim TotalRoll As Currency Dim Bal As Currency RollVal = Application.InputBox("What is the Roll DPD?", _ "Get Roll DPD Value ", Type:=1) For Each objRow In ActiveSheet.UsedRange.Rows If Cells(objRow.Row, "F").Value = RollVal Then objRow.Interior.ColorIndex = 45 Bal = Cells(objRow.Row, "G").Value TotalRoll = TotalRoll + Bal Else objRow.Interior.ColorIndex = xlNone End If Next objRow Range("J2").FormulaR1C1 = TotalRoll End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
pulling values from column g into a variable, and returningvariable on spreadsheet
On Oct 6, 12:09*pm, "Don Guillett" wrote:
Sub hiliteif() Rows.Interior.ColorIndex = 0 rollval = Application.InputBox("What is the Roll DPD?", _ * * "Get Roll DPD Value ", Type:=1) For i = 2 To Cells(Rows.Count, "f").End(xlUp).Row If Cells(i, "f") rollval Then Rows(i).Interior.ColorIndex = 45 ms = ms + Cells(i, "g") End If Next i Range("J2").Value = ms End Sub -- Don Guillett Microsoft MVP Excel SalesAid Software "Matthew Dyer" wrote in message ... Im running into some issues and I have an idea as to what the problem is but I have no idea how to solve it. The following code will highlight any rows that have a column F value = RollVal. I tried to modify it to also add up all the values of column G if the value of column F and return this value in cell J2, but my coding skills seem to be severely lacking. I think the problem is that it is trying to include the values of the header row, which is obviously not currency or any numerical value at all. Would making the objRow start with Row 2 fix this? Here is the code: Sub RollCalc() Dim RollVal As Long Dim objRow As Object Dim TotalRoll As Currency Dim Bal *As Currency RollVal = Application.InputBox("What is the Roll DPD?", _ * *"Get Roll DPD Value ", Type:=1) For Each objRow In ActiveSheet.UsedRange.Rows * *If Cells(objRow.Row, "F").Value = RollVal Then * * * *objRow.Interior.ColorIndex = 45 * * * *Bal = Cells(objRow.Row, "G").Value * * * *TotalRoll = TotalRoll + Bal * *Else * * * *objRow.Interior.ColorIndex = xlNone * *End If Next objRow Range("J2").FormulaR1C1 = TotalRoll End Sub- Hide quoted text - - Show quoted text - This works fantastic! One request though... I really liked the origional code is that it would only highlight the used range of cells. Using this code, how do I stop highlighting at the end of my range of columns? If it helps my range ends at column H. I tried the following change, but it didnt work: If Cells(i, "f") = RollVal Then Rows(i, "H").Interior.ColorIndex = 45 I get an error saying "Application-defined or object-defined error The best I could do for now is to just highlight one specific column with the following code: If Cells(i, "f") = RollVal Then Cells(i, "F").Interior.ColorIndex = 45 I also tried to do Cells(i, "A:H").Interior... but that didnt work either. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
pulling values from column g into a variable, and returning variable on spreadsheet
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning multiple values that are NOT in a single column or row | Excel Worksheet Functions | |||
column returning null values | Excel Programming | |||
Looking up data in a column, then returning values of respective row | Excel Discussion (Misc queries) | |||
Formula for Returning values in another spreadsheet | Excel Worksheet Functions | |||
returning values multi column listbox | Excel Programming |