loop overrunning first loopExcel VBA to determine last non-value (IE may have a formula but no value) row in columnExcel VBA - read cell value from codecheck is user select data from first or other columnsexcel macro to create new row for number diffUsing Application.Selection and Application.InputBox to Multiply Two ColumnsCopy data from worksheet to html file to maillast row value returns blank with only one data rowhow to read same data from two sheet then subtract the data using vbaHow to replace the data validation input message with a textboxExcel: Compare two ranges and delete duplicate cell values
How do I prevent inappropriate ads from appearing in my game?
Pre-Employment Background Check With Consent For Future Checks
Should a narrator ever describe things based on a character's view instead of facts?
Why didn't Voldemort know what Grindelwald looked like?
Can I say "fingers" when referring to toes?
Difference between shutdown options
Typing CO_2 easily
Overlapping circles covering polygon
Ways of geometrical multiplication
Limit max CPU usage SQL SERVER with WSRM
Why would five hundred and five be same as one?
Do you waste sorcery points if you try to apply metamagic to a spell from a scroll but fail to cast it?
Why does a 97 / 92 key piano exist by Bösendorfer?
Sound waves in different octaves
Is there a RAID 0 Equivalent for RAM?
How do I Interface a PS/2 Keyboard without Modern Techniques?
What is the meaning of the following sentence?
Identifying "long and narrow" polygons in with PostGIS
How to preserve electronics (computers, iPads and phones) for hundreds of years
Storage of electrolytic capacitors - how long?
What does "Scientists rise up against statistical significance" mean? (Comment in Nature)
Why does the Persian emissary display a string of crowned skulls?
Why do Radio Buttons not fill the entire outer circle?
Why the "ls" command is showing the permissions of files in a FAT32 partition?
loop overrunning first loop
Excel VBA to determine last non-value (IE may have a formula but no value) row in columnExcel VBA - read cell value from codecheck is user select data from first or other columnsexcel macro to create new row for number diffUsing Application.Selection and Application.InputBox to Multiply Two ColumnsCopy data from worksheet to html file to maillast row value returns blank with only one data rowhow to read same data from two sheet then subtract the data using vbaHow to replace the data validation input message with a textboxExcel: Compare two ranges and delete duplicate cell values
0
My code keeps running past the next cell to the next column loops, can anyone help with this? the main aim is to highlight an entire range with Input Rng and to read a red value in each of the columns and return the register number to the left in the offset of the output column For each column of each cell.
ObtainSCEs()
Dim InputRng As Range
Dim OutputRng As Range
Dim Rng As Range
xTitleID = "ObtainSCE"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("select data Range:", xTitleID, InputRng.Address, Type:=8)
Set OutputRng = Application.InputBox("select output Range:", xTitleID, Type:=8)
Dim C As Long
C = 0
Dim B As Long
B = InputRng.Columns.Count
Dim A As Long
A = 1
Dim Cell As Range
Dim Column As Range
For Each Column In InputRng
For Each Cell In Column
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng.Offset(0, 0)) > 0 Then
OutputRng.Offset(0, C).Value = OutputRng.Offset(0, C).Value & ","
OutputRng.Offset(0, C).Value = OutputRng.Offset(0, C) & Cell.Offset(0, -1 - C).Value
Else
OutputRng.Offset(0, C) = Cell.Offset(0, -1 - C).Value
End If
End If
Next Cell
Next Column
End Sub
I have a second code that i tried using a different approach but it keeps running the first column continuously. Shown below
Sub ObtainSCEs()
Dim InputRng As Range
Dim OutputRng As Range
Dim Rng As Range
xTitleID = "ObtainSCE"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("select data Range:", xTitleID, InputRng.Address, Type:=8)
Set OutputRng = Application.InputBox("select output Range:", xTitleID, Type:=8)
Dim C As Long
C = 0
Dim B As Long
B = InputRng.Columns.Count
Dim A As Long
A = 0
Dim Cell As Range
Dim Column As Range
For n = 1 To 5
InputRng.Columns(n).Select
For Each Cell In InputRng.Columns.Cells
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng.Offset(0, 0)) > 0 Then
OutputRng.Offset(0, C) = OutputRng.Offset(0, C).Value & ","
OutputRng.Offset(0, C) = OutputRng.Offset(0, C) & Cell.Offset(0, -1 - C).Value
Else
OutputRng.Offset(0, C) = Cell.Offset(0, -1 - C).Value
End If
End If
Next Cell
C = C + 1
Next n
End Sub
This is the code i'm currently using to do this, right now i'm manually selecting all 6 columns but i want to select 1 entire range then split the range into its respective columns.
Sub GetSCE()
Application.Volatile True
Dim Rng As Range
Dim InputRng1 As Range, OutputRng As Range
Dim InputRng2 As Range, InputRng3 As Range
Dim InputRng4 As Range, InputRng5 As Range
Dim InputRng6 As Range
Dim Cell As Range
Dim sev1 As Integer
sev1 = 1
Dim sev2 As Integer
sev2 = 2
Dim sev3 As Integer
sev3 = 3
Dim sev4 As Integer
sev4 = 4
Dim sev5 As Integer
sev5 = 5
Dim sev6 As Integer
sev6 = 6
xTitleID = "ObtainSCE"
Set InputRng1 = Application.Selection
Set InputRng1 = Application.InputBox("Select Data Range1:", xTitleID, InputRng1.Address, Type:=8)
Set InputRng2 = Application.InputBox("Select Data Range2:", xTitleID, Type:=8)
Set InputRng3 = Application.InputBox("Select Data Range3:", xTitleID, Type:=8)
Set InputRng4 = Application.InputBox("Select Data Range4:", xTitleID, Type:=8)
Set InputRng5 = Application.InputBox("Select Data Range5:", xTitleID, Type:=8)
Set InputRng6 = Application.InputBox("Select Data Range6:", xTitleID, Type:=8)
Set OutputRng1 = Application.InputBox("Select Starting Cells:", xTitleID, Type:=8)
Set OutputRng2 = Application.InputBox("Select Starting Cells:", xTitleID, Type:=8)
Set OutputRng3 = Application.InputBox("Select Starting Cells:", xTitleID, Type:=8)
Set OutputRng4 = Application.InputBox("Select Starting Cells:", xTitleID, Type:=8)
Set OutputRng5 = Application.InputBox("Select Starting Cells:", xTitleID, Type:=8)
Set OutputRng6 = Application.InputBox("Select Starting Cells:", xTitleID, Type:=8)
For Each Cell In InputRng1
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng1) > 0 Then OutputRng1.Value = OutputRng1.Value & ","
OutputRng1.Value = OutputRng1.Value & Cell.Offset(0, -sev1).Value
Else
End If
Next Cell
For Each Cell In InputRng2
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng2) > 0 Then OutputRng2.Value = OutputRng2.Value & ","
OutputRng2.Value = OutputRng2.Value & Cell.Offset(0, -sev2).Value
Else
End If
Next Cell
For Each Cell In InputRng3
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng3) > 0 Then OutputRng3.Value = OutputRng3.Value & ","
OutputRng3.Value = OutputRng3.Value & Cell.Offset(0, -sev3).Value
Else
End If
Next Cell
For Each Cell In InputRng4
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng4) > 0 Then OutputRng4.Value = OutputRng4.Value & ","
OutputRng4.Value = OutputRng4.Value & Cell.Offset(0, -sev4).Value
Else
End If
Next Cell
For Each Cell In InputRng5
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng5) > 0 Then OutputRng5.Value = OutputRng5.Value & ","
OutputRng5.Value = OutputRng5.Value & Cell.Offset(0, -sev5).Value
Else
End If
Next Cell
For Each Cell In InputRng6
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng6) > 0 Then OutputRng6.Value = OutputRng6.Value & ","
OutputRng6.Value = OutputRng6.Value & Cell.Offset(0, -sev6).Value
Else
End If
Next Cell
End Sub
This is what i'm trying to do if anyone needs a clearer picture
Picture of what im trying to do
Thanks for the help
excel vba
edited Mar 7 at 8:06
Pᴇʜ
24.3k63052
asked Mar 7 at 3:01
Viknesh SangarajuViknesh Sangaraju
32
|
show 1 more comment
0
My code keeps running past the next cell to the next column loops, can anyone help with this? the main aim is to highlight an entire range with Input Rng and to read a red value in each of the columns and return the register number to the left in the offset of the output column For each column of each cell.
ObtainSCEs()
Dim InputRng As Range
Dim OutputRng As Range
Dim Rng As Range
xTitleID = "ObtainSCE"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("select data Range:", xTitleID, InputRng.Address, Type:=8)
Set OutputRng = Application.InputBox("select output Range:", xTitleID, Type:=8)
Dim C As Long
C = 0
Dim B As Long
B = InputRng.Columns.Count
Dim A As Long
A = 1
Dim Cell As Range
Dim Column As Range
For Each Column In InputRng
For Each Cell In Column
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng.Offset(0, 0)) > 0 Then
OutputRng.Offset(0, C).Value = OutputRng.Offset(0, C).Value & ","
OutputRng.Offset(0, C).Value = OutputRng.Offset(0, C) & Cell.Offset(0, -1 - C).Value
Else
OutputRng.Offset(0, C) = Cell.Offset(0, -1 - C).Value
End If
End If
Next Cell
Next Column
End Sub
I have a second code that i tried using a different approach but it keeps running the first column continuously. Shown below
Sub ObtainSCEs()
Dim InputRng As Range
Dim OutputRng As Range
Dim Rng As Range
xTitleID = "ObtainSCE"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("select data Range:", xTitleID, InputRng.Address, Type:=8)
Set OutputRng = Application.InputBox("select output Range:", xTitleID, Type:=8)
Dim C As Long
C = 0
Dim B As Long
B = InputRng.Columns.Count
Dim A As Long
A = 0
Dim Cell As Range
Dim Column As Range
For n = 1 To 5
InputRng.Columns(n).Select
For Each Cell In InputRng.Columns.Cells
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng.Offset(0, 0)) > 0 Then
OutputRng.Offset(0, C) = OutputRng.Offset(0, C).Value & ","
OutputRng.Offset(0, C) = OutputRng.Offset(0, C) & Cell.Offset(0, -1 - C).Value
Else
OutputRng.Offset(0, C) = Cell.Offset(0, -1 - C).Value
End If
End If
Next Cell
C = C + 1
Next n
End Sub
This is the code i'm currently using to do this, right now i'm manually selecting all 6 columns but i want to select 1 entire range then split the range into its respective columns.
Sub GetSCE()
Application.Volatile True
Dim Rng As Range
Dim InputRng1 As Range, OutputRng As Range
Dim InputRng2 As Range, InputRng3 As Range
Dim InputRng4 As Range, InputRng5 As Range
Dim InputRng6 As Range
Dim Cell As Range
Dim sev1 As Integer
sev1 = 1
Dim sev2 As Integer
sev2 = 2
Dim sev3 As Integer
sev3 = 3
Dim sev4 As Integer
sev4 = 4
Dim sev5 As Integer
sev5 = 5
Dim sev6 As Integer
sev6 = 6
xTitleID = "ObtainSCE"
Set InputRng1 = Application.Selection
Set InputRng1 = Application.InputBox("Select Data Range1:", xTitleID, InputRng1.Address, Type:=8)
Set InputRng2 = Application.InputBox("Select Data Range2:", xTitleID, Type:=8)
Set InputRng3 = Application.InputBox("Select Data Range3:", xTitleID, Type:=8)
Set InputRng4 = Application.InputBox("Select Data Range4:", xTitleID, Type:=8)
Set InputRng5 = Application.InputBox("Select Data Range5:", xTitleID, Type:=8)
Set InputRng6 = Application.InputBox("Select Data Range6:", xTitleID, Type:=8)
Set OutputRng1 = Application.InputBox("Select Starting Cells:", xTitleID, Type:=8)
Set OutputRng2 = Application.InputBox("Select Starting Cells:", xTitleID, Type:=8)
Set OutputRng3 = Application.InputBox("Select Starting Cells:", xTitleID, Type:=8)
Set OutputRng4 = Application.InputBox("Select Starting Cells:", xTitleID, Type:=8)
Set OutputRng5 = Application.InputBox("Select Starting Cells:", xTitleID, Type:=8)
Set OutputRng6 = Application.InputBox("Select Starting Cells:", xTitleID, Type:=8)
For Each Cell In InputRng1
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng1) > 0 Then OutputRng1.Value = OutputRng1.Value & ","
OutputRng1.Value = OutputRng1.Value & Cell.Offset(0, -sev1).Value
Else
End If
Next Cell
For Each Cell In InputRng2
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng2) > 0 Then OutputRng2.Value = OutputRng2.Value & ","
OutputRng2.Value = OutputRng2.Value & Cell.Offset(0, -sev2).Value
Else
End If
Next Cell
For Each Cell In InputRng3
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng3) > 0 Then OutputRng3.Value = OutputRng3.Value & ","
OutputRng3.Value = OutputRng3.Value & Cell.Offset(0, -sev3).Value
Else
End If
Next Cell
For Each Cell In InputRng4
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng4) > 0 Then OutputRng4.Value = OutputRng4.Value & ","
OutputRng4.Value = OutputRng4.Value & Cell.Offset(0, -sev4).Value
Else
End If
Next Cell
For Each Cell In InputRng5
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng5) > 0 Then OutputRng5.Value = OutputRng5.Value & ","
OutputRng5.Value = OutputRng5.Value & Cell.Offset(0, -sev5).Value
Else
End If
Next Cell
For Each Cell In InputRng6
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng6) > 0 Then OutputRng6.Value = OutputRng6.Value & ","
OutputRng6.Value = OutputRng6.Value & Cell.Offset(0, -sev6).Value
Else
End If
Next Cell
End Sub
This is what i'm trying to do if anyone needs a clearer picture
Picture of what im trying to do
Thanks for the help
excel vba
edited Mar 7 at 8:06
Pᴇʜ
24.3k63052
asked Mar 7 at 3:01
Viknesh SangarajuViknesh Sangaraju
32
Could not understand your objective clearly. However C is always zero, What do you want the c Value to be if red cell is found?
– Ahmed AU
Mar 7 at 4:04
c is supposed to +1 to original value once each column is completed but i cant get there for now so i left it out intentionally, i edited it into the second code so you could see where its supposed to be, which part could you not understand ill try ot explain it more clearly,
– Viknesh Sangaraju
Mar 7 at 4:14
Do you need each row to be gone through individually or do you just need to go through each cell? Furthermore, it makes no sense to go through each cell in a cell... For each Column in InputRng is not the column your going through. With this for each you go through each cell in InputRng.
– Kajkrow
Mar 7 at 7:19
i would need to go through Each cell in Each column of the desired range but the desired returned value is the row label in the first column ( i Assume you've seen the picture). Oh what should it be if i want to go through each column in InputRng?
– Viknesh Sangaraju
Mar 7 at 7:23
I cannot see the picture since I'm at work and it is blocked.
– Kajkrow
Mar 7 at 7:24
|
show 1 more comment
0
0
0
My code keeps running past the next cell to the next column loops, can anyone help with this? the main aim is to highlight an entire range with Input Rng and to read a red value in each of the columns and return the register number to the left in the offset of the output column For each column of each cell.
ObtainSCEs()
Dim InputRng As Range
Dim OutputRng As Range
Dim Rng As Range
xTitleID = "ObtainSCE"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("select data Range:", xTitleID, InputRng.Address, Type:=8)
Set OutputRng = Application.InputBox("select output Range:", xTitleID, Type:=8)
Dim C As Long
C = 0
Dim B As Long
B = InputRng.Columns.Count
Dim A As Long
A = 1
Dim Cell As Range
Dim Column As Range
For Each Column In InputRng
For Each Cell In Column
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng.Offset(0, 0)) > 0 Then
OutputRng.Offset(0, C).Value = OutputRng.Offset(0, C).Value & ","
OutputRng.Offset(0, C).Value = OutputRng.Offset(0, C) & Cell.Offset(0, -1 - C).Value
Else
OutputRng.Offset(0, C) = Cell.Offset(0, -1 - C).Value
End If
End If
Next Cell
Next Column
End Sub
I have a second code that i tried using a different approach but it keeps running the first column continuously. Shown below
Sub ObtainSCEs()
Dim InputRng As Range
Dim OutputRng As Range
Dim Rng As Range
xTitleID = "ObtainSCE"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("select data Range:", xTitleID, InputRng.Address, Type:=8)
Set OutputRng = Application.InputBox("select output Range:", xTitleID, Type:=8)
Dim C As Long
C = 0
Dim B As Long
B = InputRng.Columns.Count
Dim A As Long
A = 0
Dim Cell As Range
Dim Column As Range
For n = 1 To 5
InputRng.Columns(n).Select
For Each Cell In InputRng.Columns.Cells
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng.Offset(0, 0)) > 0 Then
OutputRng.Offset(0, C) = OutputRng.Offset(0, C).Value & ","
OutputRng.Offset(0, C) = OutputRng.Offset(0, C) & Cell.Offset(0, -1 - C).Value
Else
OutputRng.Offset(0, C) = Cell.Offset(0, -1 - C).Value
End If
End If
Next Cell
C = C + 1
Next n
End Sub
This is the code i'm currently using to do this, right now i'm manually selecting all 6 columns but i want to select 1 entire range then split the range into its respective columns.
Sub GetSCE()
Application.Volatile True
Dim Rng As Range
Dim InputRng1 As Range, OutputRng As Range
Dim InputRng2 As Range, InputRng3 As Range
Dim InputRng4 As Range, InputRng5 As Range
Dim InputRng6 As Range
Dim Cell As Range
Dim sev1 As Integer
sev1 = 1
Dim sev2 As Integer
sev2 = 2
Dim sev3 As Integer
sev3 = 3
Dim sev4 As Integer
sev4 = 4
Dim sev5 As Integer
sev5 = 5
Dim sev6 As Integer
sev6 = 6
xTitleID = "ObtainSCE"
Set InputRng1 = Application.Selection
Set InputRng1 = Application.InputBox("Select Data Range1:", xTitleID, InputRng1.Address, Type:=8)
Set InputRng2 = Application.InputBox("Select Data Range2:", xTitleID, Type:=8)
Set InputRng3 = Application.InputBox("Select Data Range3:", xTitleID, Type:=8)
Set InputRng4 = Application.InputBox("Select Data Range4:", xTitleID, Type:=8)
Set InputRng5 = Application.InputBox("Select Data Range5:", xTitleID, Type:=8)
Set InputRng6 = Application.InputBox("Select Data Range6:", xTitleID, Type:=8)
Set OutputRng1 = Application.InputBox("Select Starting Cells:", xTitleID, Type:=8)
Set OutputRng2 = Application.InputBox("Select Starting Cells:", xTitleID, Type:=8)
Set OutputRng3 = Application.InputBox("Select Starting Cells:", xTitleID, Type:=8)
Set OutputRng4 = Application.InputBox("Select Starting Cells:", xTitleID, Type:=8)
Set OutputRng5 = Application.InputBox("Select Starting Cells:", xTitleID, Type:=8)
Set OutputRng6 = Application.InputBox("Select Starting Cells:", xTitleID, Type:=8)
For Each Cell In InputRng1
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng1) > 0 Then OutputRng1.Value = OutputRng1.Value & ","
OutputRng1.Value = OutputRng1.Value & Cell.Offset(0, -sev1).Value
Else
End If
Next Cell
For Each Cell In InputRng2
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng2) > 0 Then OutputRng2.Value = OutputRng2.Value & ","
OutputRng2.Value = OutputRng2.Value & Cell.Offset(0, -sev2).Value
Else
End If
Next Cell
For Each Cell In InputRng3
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng3) > 0 Then OutputRng3.Value = OutputRng3.Value & ","
OutputRng3.Value = OutputRng3.Value & Cell.Offset(0, -sev3).Value
Else
End If
Next Cell
For Each Cell In InputRng4
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng4) > 0 Then OutputRng4.Value = OutputRng4.Value & ","
OutputRng4.Value = OutputRng4.Value & Cell.Offset(0, -sev4).Value
Else
End If
Next Cell
For Each Cell In InputRng5
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng5) > 0 Then OutputRng5.Value = OutputRng5.Value & ","
OutputRng5.Value = OutputRng5.Value & Cell.Offset(0, -sev5).Value
Else
End If
Next Cell
For Each Cell In InputRng6
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng6) > 0 Then OutputRng6.Value = OutputRng6.Value & ","
OutputRng6.Value = OutputRng6.Value & Cell.Offset(0, -sev6).Value
Else
End If
Next Cell
End Sub
This is what i'm trying to do if anyone needs a clearer picture
Picture of what im trying to do
Thanks for the help
excel vba
edited Mar 7 at 8:06
Pᴇʜ
24.3k63052
asked Mar 7 at 3:01
Viknesh SangarajuViknesh Sangaraju
32
My code keeps running past the next cell to the next column loops, can anyone help with this? the main aim is to highlight an entire range with Input Rng and to read a red value in each of the columns and return the register number to the left in the offset of the output column For each column of each cell.
ObtainSCEs()
Dim InputRng As Range
Dim OutputRng As Range
Dim Rng As Range
xTitleID = "ObtainSCE"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("select data Range:", xTitleID, InputRng.Address, Type:=8)
Set OutputRng = Application.InputBox("select output Range:", xTitleID, Type:=8)
Dim C As Long
C = 0
Dim B As Long
B = InputRng.Columns.Count
Dim A As Long
A = 1
Dim Cell As Range
Dim Column As Range
For Each Column In InputRng
For Each Cell In Column
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng.Offset(0, 0)) > 0 Then
OutputRng.Offset(0, C).Value = OutputRng.Offset(0, C).Value & ","
OutputRng.Offset(0, C).Value = OutputRng.Offset(0, C) & Cell.Offset(0, -1 - C).Value
Else
OutputRng.Offset(0, C) = Cell.Offset(0, -1 - C).Value
End If
End If
Next Cell
Next Column
End Sub
I have a second code that i tried using a different approach but it keeps running the first column continuously. Shown below
Sub ObtainSCEs()
Dim InputRng As Range
Dim OutputRng As Range
Dim Rng As Range
xTitleID = "ObtainSCE"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("select data Range:", xTitleID, InputRng.Address, Type:=8)
Set OutputRng = Application.InputBox("select output Range:", xTitleID, Type:=8)
Dim C As Long
C = 0
Dim B As Long
B = InputRng.Columns.Count
Dim A As Long
A = 0
Dim Cell As Range
Dim Column As Range
For n = 1 To 5
InputRng.Columns(n).Select
For Each Cell In InputRng.Columns.Cells
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng.Offset(0, 0)) > 0 Then
OutputRng.Offset(0, C) = OutputRng.Offset(0, C).Value & ","
OutputRng.Offset(0, C) = OutputRng.Offset(0, C) & Cell.Offset(0, -1 - C).Value
Else
OutputRng.Offset(0, C) = Cell.Offset(0, -1 - C).Value
End If
End If
Next Cell
C = C + 1
Next n
End Sub
This is the code i'm currently using to do this, right now i'm manually selecting all 6 columns but i want to select 1 entire range then split the range into its respective columns.
Sub GetSCE()
Application.Volatile True
Dim Rng As Range
Dim InputRng1 As Range, OutputRng As Range
Dim InputRng2 As Range, InputRng3 As Range
Dim InputRng4 As Range, InputRng5 As Range
Dim InputRng6 As Range
Dim Cell As Range
Dim sev1 As Integer
sev1 = 1
Dim sev2 As Integer
sev2 = 2
Dim sev3 As Integer
sev3 = 3
Dim sev4 As Integer
sev4 = 4
Dim sev5 As Integer
sev5 = 5
Dim sev6 As Integer
sev6 = 6
xTitleID = "ObtainSCE"
Set InputRng1 = Application.Selection
Set InputRng1 = Application.InputBox("Select Data Range1:", xTitleID, InputRng1.Address, Type:=8)
Set InputRng2 = Application.InputBox("Select Data Range2:", xTitleID, Type:=8)
Set InputRng3 = Application.InputBox("Select Data Range3:", xTitleID, Type:=8)
Set InputRng4 = Application.InputBox("Select Data Range4:", xTitleID, Type:=8)
Set InputRng5 = Application.InputBox("Select Data Range5:", xTitleID, Type:=8)
Set InputRng6 = Application.InputBox("Select Data Range6:", xTitleID, Type:=8)
Set OutputRng1 = Application.InputBox("Select Starting Cells:", xTitleID, Type:=8)
Set OutputRng2 = Application.InputBox("Select Starting Cells:", xTitleID, Type:=8)
Set OutputRng3 = Application.InputBox("Select Starting Cells:", xTitleID, Type:=8)
Set OutputRng4 = Application.InputBox("Select Starting Cells:", xTitleID, Type:=8)
Set OutputRng5 = Application.InputBox("Select Starting Cells:", xTitleID, Type:=8)
Set OutputRng6 = Application.InputBox("Select Starting Cells:", xTitleID, Type:=8)
For Each Cell In InputRng1
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng1) > 0 Then OutputRng1.Value = OutputRng1.Value & ","
OutputRng1.Value = OutputRng1.Value & Cell.Offset(0, -sev1).Value
Else
End If
Next Cell
For Each Cell In InputRng2
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng2) > 0 Then OutputRng2.Value = OutputRng2.Value & ","
OutputRng2.Value = OutputRng2.Value & Cell.Offset(0, -sev2).Value
Else
End If
Next Cell
For Each Cell In InputRng3
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng3) > 0 Then OutputRng3.Value = OutputRng3.Value & ","
OutputRng3.Value = OutputRng3.Value & Cell.Offset(0, -sev3).Value
Else
End If
Next Cell
For Each Cell In InputRng4
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng4) > 0 Then OutputRng4.Value = OutputRng4.Value & ","
OutputRng4.Value = OutputRng4.Value & Cell.Offset(0, -sev4).Value
Else
End If
Next Cell
For Each Cell In InputRng5
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng5) > 0 Then OutputRng5.Value = OutputRng5.Value & ","
OutputRng5.Value = OutputRng5.Value & Cell.Offset(0, -sev5).Value
Else
End If
Next Cell
For Each Cell In InputRng6
If Cell.DisplayFormat.Interior.ColorIndex = 3 Then
If Len(OutputRng6) > 0 Then OutputRng6.Value = OutputRng6.Value & ","
OutputRng6.Value = OutputRng6.Value & Cell.Offset(0, -sev6).Value
Else
End If
Next Cell
End Sub
This is what i'm trying to do if anyone needs a clearer picture
Picture of what im trying to do
Thanks for the help
excel vba
excel vba
edited Mar 7 at 8:06
Pᴇʜ
24.3k63052
asked Mar 7 at 3:01
Viknesh SangarajuViknesh Sangaraju
32
edited Mar 7 at 8:06
Pᴇʜ
24.3k63052
asked Mar 7 at 3:01
Viknesh SangarajuViknesh Sangaraju
32
edited Mar 7 at 8:06
Pᴇʜ
24.3k63052
edited Mar 7 at 8:06
Pᴇʜ
24.3k63052
edited Mar 7 at 8:06
Pᴇʜ
24.3k63052
24.3k63052
asked Mar 7 at 3:01
Viknesh SangarajuViknesh Sangaraju
32
asked Mar 7 at 3:01
Viknesh SangarajuViknesh Sangaraju
32
asked Mar 7 at 3:01
Viknesh SangarajuViknesh Sangaraju
32
32
Could not understand your objective clearly. However C is always zero, What do you want the c Value to be if red cell is found?
– Ahmed AU
Mar 7 at 4:04
c is supposed to +1 to original value once each column is completed but i cant get there for now so i left it out intentionally, i edited it into the second code so you could see where its supposed to be, which part could you not understand ill try ot explain it more clearly,
– Viknesh Sangaraju
Mar 7 at 4:14
Do you need each row to be gone through individually or do you just need to go through each cell? Furthermore, it makes no sense to go through each cell in a cell... For each Column in InputRng is not the column your going through. With this for each you go through each cell in InputRng.
– Kajkrow
Mar 7 at 7:19
i would need to go through Each cell in Each column of the desired range but the desired returned value is the row label in the first column ( i Assume you've seen the picture). Oh what should it be if i want to go through each column in InputRng?
– Viknesh Sangaraju
Mar 7 at 7:23
I cannot see the picture since I'm at work and it is blocked.
– Kajkrow
Mar 7 at 7:24
|
show 1 more comment
Could not understand your objective clearly. However C is always zero, What do you want the c Value to be if red cell is found?
– Ahmed AU
Mar 7 at 4:04
c is supposed to +1 to original value once each column is completed but i cant get there for now so i left it out intentionally, i edited it into the second code so you could see where its supposed to be, which part could you not understand ill try ot explain it more clearly,
– Viknesh Sangaraju
Mar 7 at 4:14
Do you need each row to be gone through individually or do you just need to go through each cell? Furthermore, it makes no sense to go through each cell in a cell... For each Column in InputRng is not the column your going through. With this for each you go through each cell in InputRng.
– Kajkrow
Mar 7 at 7:19
i would need to go through Each cell in Each column of the desired range but the desired returned value is the row label in the first column ( i Assume you've seen the picture). Oh what should it be if i want to go through each column in InputRng?
– Viknesh Sangaraju
Mar 7 at 7:23
I cannot see the picture since I'm at work and it is blocked.
– Kajkrow
Mar 7 at 7:24
Could not understand your objective clearly. However C is always zero, What do you want the c Value to be if red cell is found?
– Ahmed AU
Mar 7 at 4:04
Could not understand your objective clearly. However C is always zero, What do you want the c Value to be if red cell is found?
– Ahmed AU
Mar 7 at 4:04
c is supposed to +1 to original value once each column is completed but i cant get there for now so i left it out intentionally, i edited it into the second code so you could see where its supposed to be, which part could you not understand ill try ot explain it more clearly,
– Viknesh Sangaraju
Mar 7 at 4:14
c is supposed to +1 to original value once each column is completed but i cant get there for now so i left it out intentionally, i edited it into the second code so you could see where its supposed to be, which part could you not understand ill try ot explain it more clearly,
– Viknesh Sangaraju
Mar 7 at 4:14
Do you need each row to be gone through individually or do you just need to go through each cell? Furthermore, it makes no sense to go through each cell in a cell... For each Column in InputRng is not the column your going through. With this for each you go through each cell in InputRng.
– Kajkrow
Mar 7 at 7:19
Do you need each row to be gone through individually or do you just need to go through each cell? Furthermore, it makes no sense to go through each cell in a cell... For each Column in InputRng is not the column your going through. With this for each you go through each cell in InputRng.
– Kajkrow
Mar 7 at 7:19
i would need to go through Each cell in Each column of the desired range but the desired returned value is the row label in the first column ( i Assume you've seen the picture). Oh what should it be if i want to go through each column in InputRng?
– Viknesh Sangaraju
Mar 7 at 7:23
i would need to go through Each cell in Each column of the desired range but the desired returned value is the row label in the first column ( i Assume you've seen the picture). Oh what should it be if i want to go through each column in InputRng?
– Viknesh Sangaraju
Mar 7 at 7:23
I cannot see the picture since I'm at work and it is blocked.
– Kajkrow
Mar 7 at 7:24
I cannot see the picture since I'm at work and it is blocked.
– Kajkrow
Mar 7 at 7:24
|
show 1 more comment
2 Answers
2
active
oldest
votes
0
May try
Sub ObtainSCEs()
Dim InputRng As Range
Dim OutputRng As Range
Dim Rw As Long
Dim Col As Long
xTitleID = "ObtainSCE"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("select data Range:", xTitleID, InputRng.Address, Type:=8)
Set OutputRng = Application.InputBox("select output Range:", xTitleID, Type:=8)
Dim A As Long
Dim B As Long
Dim C As Long
Dim Cell As Range
Dim Column As Range
For Col = 1 To InputRng.Columns.Count
For Rw = 1 To InputRng.Rows.Count
If InputRng(Rw, Col).Interior.ColorIndex = 3 Then
Valx = InputRng(Rw, 1).Offset(0, -1).Value
If Len(OutputRng.Offset(0, Col - 1)) > 0 Then
OutputRng.Offset(0, Col - 1).Value = OutputRng.Offset(0, Col - 1).Value & "," & Valx
Else
OutputRng.Offset(0, Col - 1) = Valx
End If
End If
Next Rw
Next Col
End Sub
Input range selection excludes Row labels column, for output range selection of first cell of the destination range will do.
answered Mar 7 at 4:19
Ahmed AUAhmed AU
1,2121210
The reference numbers are all in the same row in the far left while the highlighted values are the highest numbers in each column ( Its data in a pivot table) and my output comes out in one line , would you like me to post the current code that i am using that works but is tedious to use? it might give you a better understanding of what im trying to do
– Viknesh Sangaraju
Mar 7 at 4:29
Edited answer may pl feedback
– Ahmed AU
Mar 7 at 8:09
its not reading the color and going into the code from Valx and skips straight to Next rw even if the cell is red.
– Viknesh Sangaraju
Mar 7 at 8:40
May use .DisplayFormatas in OP in case of conditional formatting. Also DeclareValx( or assign directly) and remove unused declarations
– Ahmed AU
Mar 7 at 8:53
Ok i found out why and got it to work, Thanks for your help , you were missing a display format Before your Interior.color. Thanks so much, just want to know , is it better to use for loops and not for each loops?
– Viknesh Sangaraju
Mar 7 at 8:54
|
show 1 more comment
0
This way you go through each column.
Sub ObtainSCEs()
Dim InRng As Range
Dim OutRng As Range
BoxTitle = "ObtainSCE"
Set InRng = Application.InputBox("Select Data Input Range", BoxTitle, , Type:=8)
Set OutRng = Application.InputBox("Select Data Output Range", BoxTitle, , Type:=8)
Dim cll As Range
Dim col As Range
For Each col In InRng.Columns
For Each cll In InRng
If cll.Column = col.Column Then
'...
'whatever you want to do
'...
End If
Next cll
Next col
End Sub
answered Mar 7 at 8:03
KajkrowKajkrow
16713
Im getting an error in the (If cll.Column = Col.Column Then)Line It says runtime error 91, object variable or block with block variable not set.
– Viknesh Sangaraju
Mar 7 at 8:26
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55035384%2floop-overrunning-first-loop%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
2 Answers
2
active
oldest
votes
2 Answers
2
active
oldest
votes
active
oldest
votes
active
oldest
votes
0
May try
Sub ObtainSCEs()
Dim InputRng As Range
Dim OutputRng As Range
Dim Rw As Long
Dim Col As Long
xTitleID = "ObtainSCE"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("select data Range:", xTitleID, InputRng.Address, Type:=8)
Set OutputRng = Application.InputBox("select output Range:", xTitleID, Type:=8)
Dim A As Long
Dim B As Long
Dim C As Long
Dim Cell As Range
Dim Column As Range
For Col = 1 To InputRng.Columns.Count
For Rw = 1 To InputRng.Rows.Count
If InputRng(Rw, Col).Interior.ColorIndex = 3 Then
Valx = InputRng(Rw, 1).Offset(0, -1).Value
If Len(OutputRng.Offset(0, Col - 1)) > 0 Then
OutputRng.Offset(0, Col - 1).Value = OutputRng.Offset(0, Col - 1).Value & "," & Valx
Else
OutputRng.Offset(0, Col - 1) = Valx
End If
End If
Next Rw
Next Col
End Sub
Input range selection excludes Row labels column, for output range selection of first cell of the destination range will do.
answered Mar 7 at 4:19
Ahmed AUAhmed AU
1,2121210
The reference numbers are all in the same row in the far left while the highlighted values are the highest numbers in each column ( Its data in a pivot table) and my output comes out in one line , would you like me to post the current code that i am using that works but is tedious to use? it might give you a better understanding of what im trying to do
– Viknesh Sangaraju
Mar 7 at 4:29
Edited answer may pl feedback
– Ahmed AU
Mar 7 at 8:09
its not reading the color and going into the code from Valx and skips straight to Next rw even if the cell is red.
– Viknesh Sangaraju
Mar 7 at 8:40
May use .DisplayFormatas in OP in case of conditional formatting. Also DeclareValx( or assign directly) and remove unused declarations
– Ahmed AU
Mar 7 at 8:53
Ok i found out why and got it to work, Thanks for your help , you were missing a display format Before your Interior.color. Thanks so much, just want to know , is it better to use for loops and not for each loops?
– Viknesh Sangaraju
Mar 7 at 8:54
|
show 1 more comment
0
May try
Sub ObtainSCEs()
Dim InputRng As Range
Dim OutputRng As Range
Dim Rw As Long
Dim Col As Long
xTitleID = "ObtainSCE"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("select data Range:", xTitleID, InputRng.Address, Type:=8)
Set OutputRng = Application.InputBox("select output Range:", xTitleID, Type:=8)
Dim A As Long
Dim B As Long
Dim C As Long
Dim Cell As Range
Dim Column As Range
For Col = 1 To InputRng.Columns.Count
For Rw = 1 To InputRng.Rows.Count
If InputRng(Rw, Col).Interior.ColorIndex = 3 Then
Valx = InputRng(Rw, 1).Offset(0, -1).Value
If Len(OutputRng.Offset(0, Col - 1)) > 0 Then
OutputRng.Offset(0, Col - 1).Value = OutputRng.Offset(0, Col - 1).Value & "," & Valx
Else
OutputRng.Offset(0, Col - 1) = Valx
End If
End If
Next Rw
Next Col
End Sub
Input range selection excludes Row labels column, for output range selection of first cell of the destination range will do.
answered Mar 7 at 4:19
Ahmed AUAhmed AU
1,2121210
The reference numbers are all in the same row in the far left while the highlighted values are the highest numbers in each column ( Its data in a pivot table) and my output comes out in one line , would you like me to post the current code that i am using that works but is tedious to use? it might give you a better understanding of what im trying to do
– Viknesh Sangaraju
Mar 7 at 4:29
Edited answer may pl feedback
– Ahmed AU
Mar 7 at 8:09
its not reading the color and going into the code from Valx and skips straight to Next rw even if the cell is red.
– Viknesh Sangaraju
Mar 7 at 8:40
May use .DisplayFormatas in OP in case of conditional formatting. Also DeclareValx( or assign directly) and remove unused declarations
– Ahmed AU
Mar 7 at 8:53
Ok i found out why and got it to work, Thanks for your help , you were missing a display format Before your Interior.color. Thanks so much, just want to know , is it better to use for loops and not for each loops?
– Viknesh Sangaraju
Mar 7 at 8:54
|
show 1 more comment
0
0
0
May try
Sub ObtainSCEs()
Dim InputRng As Range
Dim OutputRng As Range
Dim Rw As Long
Dim Col As Long
xTitleID = "ObtainSCE"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("select data Range:", xTitleID, InputRng.Address, Type:=8)
Set OutputRng = Application.InputBox("select output Range:", xTitleID, Type:=8)
Dim A As Long
Dim B As Long
Dim C As Long
Dim Cell As Range
Dim Column As Range
For Col = 1 To InputRng.Columns.Count
For Rw = 1 To InputRng.Rows.Count
If InputRng(Rw, Col).Interior.ColorIndex = 3 Then
Valx = InputRng(Rw, 1).Offset(0, -1).Value
If Len(OutputRng.Offset(0, Col - 1)) > 0 Then
OutputRng.Offset(0, Col - 1).Value = OutputRng.Offset(0, Col - 1).Value & "," & Valx
Else
OutputRng.Offset(0, Col - 1) = Valx
End If
End If
Next Rw
Next Col
End Sub
Input range selection excludes Row labels column, for output range selection of first cell of the destination range will do.
answered Mar 7 at 4:19
Ahmed AUAhmed AU
1,2121210
May try
Sub ObtainSCEs()
Dim InputRng As Range
Dim OutputRng As Range
Dim Rw As Long
Dim Col As Long
xTitleID = "ObtainSCE"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("select data Range:", xTitleID, InputRng.Address, Type:=8)
Set OutputRng = Application.InputBox("select output Range:", xTitleID, Type:=8)
Dim A As Long
Dim B As Long
Dim C As Long
Dim Cell As Range
Dim Column As Range
For Col = 1 To InputRng.Columns.Count
For Rw = 1 To InputRng.Rows.Count
If InputRng(Rw, Col).Interior.ColorIndex = 3 Then
Valx = InputRng(Rw, 1).Offset(0, -1).Value
If Len(OutputRng.Offset(0, Col - 1)) > 0 Then
OutputRng.Offset(0, Col - 1).Value = OutputRng.Offset(0, Col - 1).Value & "," & Valx
Else
OutputRng.Offset(0, Col - 1) = Valx
End If
End If
Next Rw
Next Col
End Sub
Input range selection excludes Row labels column, for output range selection of first cell of the destination range will do.
answered Mar 7 at 4:19
Ahmed AUAhmed AU
1,2121210
edited Mar 7 at 8:13
answered Mar 7 at 4:19
Ahmed AUAhmed AU
1,2121210
answered Mar 7 at 4:19
Ahmed AUAhmed AU
1,2121210
answered Mar 7 at 4:19
Ahmed AUAhmed AU
1,2121210
1,2121210
The reference numbers are all in the same row in the far left while the highlighted values are the highest numbers in each column ( Its data in a pivot table) and my output comes out in one line , would you like me to post the current code that i am using that works but is tedious to use? it might give you a better understanding of what im trying to do
– Viknesh Sangaraju
Mar 7 at 4:29
Edited answer may pl feedback
– Ahmed AU
Mar 7 at 8:09
its not reading the color and going into the code from Valx and skips straight to Next rw even if the cell is red.
– Viknesh Sangaraju
Mar 7 at 8:40
May use .DisplayFormatas in OP in case of conditional formatting. Also DeclareValx( or assign directly) and remove unused declarations
– Ahmed AU
Mar 7 at 8:53
Ok i found out why and got it to work, Thanks for your help , you were missing a display format Before your Interior.color. Thanks so much, just want to know , is it better to use for loops and not for each loops?
– Viknesh Sangaraju
Mar 7 at 8:54
|
show 1 more comment
The reference numbers are all in the same row in the far left while the highlighted values are the highest numbers in each column ( Its data in a pivot table) and my output comes out in one line , would you like me to post the current code that i am using that works but is tedious to use? it might give you a better understanding of what im trying to do
– Viknesh Sangaraju
Mar 7 at 4:29
Edited answer may pl feedback
– Ahmed AU
Mar 7 at 8:09
its not reading the color and going into the code from Valx and skips straight to Next rw even if the cell is red.
– Viknesh Sangaraju
Mar 7 at 8:40
May use .DisplayFormatas in OP in case of conditional formatting. Also DeclareValx( or assign directly) and remove unused declarations
– Ahmed AU
Mar 7 at 8:53
Ok i found out why and got it to work, Thanks for your help , you were missing a display format Before your Interior.color. Thanks so much, just want to know , is it better to use for loops and not for each loops?
– Viknesh Sangaraju
Mar 7 at 8:54
The reference numbers are all in the same row in the far left while the highlighted values are the highest numbers in each column ( Its data in a pivot table) and my output comes out in one line , would you like me to post the current code that i am using that works but is tedious to use? it might give you a better understanding of what im trying to do
– Viknesh Sangaraju
Mar 7 at 4:29
The reference numbers are all in the same row in the far left while the highlighted values are the highest numbers in each column ( Its data in a pivot table) and my output comes out in one line , would you like me to post the current code that i am using that works but is tedious to use? it might give you a better understanding of what im trying to do
– Viknesh Sangaraju
Mar 7 at 4:29
Edited answer may pl feedback
– Ahmed AU
Mar 7 at 8:09
Edited answer may pl feedback
– Ahmed AU
Mar 7 at 8:09
its not reading the color and going into the code from Valx and skips straight to Next rw even if the cell is red.
– Viknesh Sangaraju
Mar 7 at 8:40
its not reading the color and going into the code from Valx and skips straight to Next rw even if the cell is red.
– Viknesh Sangaraju
Mar 7 at 8:40
May use .
DisplayFormat as in OP in case of conditional formatting. Also Declare Valx ( or assign directly) and remove unused declarations– Ahmed AU
Mar 7 at 8:53
May use .
DisplayFormat as in OP in case of conditional formatting. Also Declare Valx ( or assign directly) and remove unused declarations– Ahmed AU
Mar 7 at 8:53
Ok i found out why and got it to work, Thanks for your help , you were missing a display format Before your Interior.color. Thanks so much, just want to know , is it better to use for loops and not for each loops?
– Viknesh Sangaraju
Mar 7 at 8:54
Ok i found out why and got it to work, Thanks for your help , you were missing a display format Before your Interior.color. Thanks so much, just want to know , is it better to use for loops and not for each loops?
– Viknesh Sangaraju
Mar 7 at 8:54
|
show 1 more comment
0
This way you go through each column.
Sub ObtainSCEs()
Dim InRng As Range
Dim OutRng As Range
BoxTitle = "ObtainSCE"
Set InRng = Application.InputBox("Select Data Input Range", BoxTitle, , Type:=8)
Set OutRng = Application.InputBox("Select Data Output Range", BoxTitle, , Type:=8)
Dim cll As Range
Dim col As Range
For Each col In InRng.Columns
For Each cll In InRng
If cll.Column = col.Column Then
'...
'whatever you want to do
'...
End If
Next cll
Next col
End Sub
answered Mar 7 at 8:03
KajkrowKajkrow
16713
Im getting an error in the (If cll.Column = Col.Column Then)Line It says runtime error 91, object variable or block with block variable not set.
– Viknesh Sangaraju
Mar 7 at 8:26
add a comment |
0
This way you go through each column.
Sub ObtainSCEs()
Dim InRng As Range
Dim OutRng As Range
BoxTitle = "ObtainSCE"
Set InRng = Application.InputBox("Select Data Input Range", BoxTitle, , Type:=8)
Set OutRng = Application.InputBox("Select Data Output Range", BoxTitle, , Type:=8)
Dim cll As Range
Dim col As Range
For Each col In InRng.Columns
For Each cll In InRng
If cll.Column = col.Column Then
'...
'whatever you want to do
'...
End If
Next cll
Next col
End Sub
answered Mar 7 at 8:03
KajkrowKajkrow
16713
Im getting an error in the (If cll.Column = Col.Column Then)Line It says runtime error 91, object variable or block with block variable not set.
– Viknesh Sangaraju
Mar 7 at 8:26
add a comment |
0
0
0
This way you go through each column.
Sub ObtainSCEs()
Dim InRng As Range
Dim OutRng As Range
BoxTitle = "ObtainSCE"
Set InRng = Application.InputBox("Select Data Input Range", BoxTitle, , Type:=8)
Set OutRng = Application.InputBox("Select Data Output Range", BoxTitle, , Type:=8)
Dim cll As Range
Dim col As Range
For Each col In InRng.Columns
For Each cll In InRng
If cll.Column = col.Column Then
'...
'whatever you want to do
'...
End If
Next cll
Next col
End Sub
answered Mar 7 at 8:03
KajkrowKajkrow
16713
This way you go through each column.
Sub ObtainSCEs()
Dim InRng As Range
Dim OutRng As Range
BoxTitle = "ObtainSCE"
Set InRng = Application.InputBox("Select Data Input Range", BoxTitle, , Type:=8)
Set OutRng = Application.InputBox("Select Data Output Range", BoxTitle, , Type:=8)
Dim cll As Range
Dim col As Range
For Each col In InRng.Columns
For Each cll In InRng
If cll.Column = col.Column Then
'...
'whatever you want to do
'...
End If
Next cll
Next col
End Sub
answered Mar 7 at 8:03
KajkrowKajkrow
16713
answered Mar 7 at 8:03
KajkrowKajkrow
16713
answered Mar 7 at 8:03
KajkrowKajkrow
16713
answered Mar 7 at 8:03
KajkrowKajkrow
16713
16713
Im getting an error in the (If cll.Column = Col.Column Then)Line It says runtime error 91, object variable or block with block variable not set.
– Viknesh Sangaraju
Mar 7 at 8:26
add a comment |
Im getting an error in the (If cll.Column = Col.Column Then)Line It says runtime error 91, object variable or block with block variable not set.
– Viknesh Sangaraju
Mar 7 at 8:26
Im getting an error in the (If cll.Column = Col.Column Then)Line It says runtime error 91, object variable or block with block variable not set.
– Viknesh Sangaraju
Mar 7 at 8:26
Im getting an error in the (If cll.Column = Col.Column Then)Line It says runtime error 91, object variable or block with block variable not set.
– Viknesh Sangaraju
Mar 7 at 8:26
add a comment |
draft saved
draft discarded
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
draft saved
draft discarded
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55035384%2floop-overrunning-first-loop%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Could not understand your objective clearly. However C is always zero, What do you want the c Value to be if red cell is found?
– Ahmed AU
Mar 7 at 4:04
c is supposed to +1 to original value once each column is completed but i cant get there for now so i left it out intentionally, i edited it into the second code so you could see where its supposed to be, which part could you not understand ill try ot explain it more clearly,
– Viknesh Sangaraju
Mar 7 at 4:14
Do you need each row to be gone through individually or do you just need to go through each cell? Furthermore, it makes no sense to go through each cell in a cell... For each Column in InputRng is not the column your going through. With this for each you go through each cell in InputRng.
– Kajkrow
Mar 7 at 7:19
i would need to go through Each cell in Each column of the desired range but the desired returned value is the row label in the first column ( i Assume you've seen the picture). Oh what should it be if i want to go through each column in InputRng?
– Viknesh Sangaraju
Mar 7 at 7:23
I cannot see the picture since I'm at work and it is blocked.
– Kajkrow
Mar 7 at 7:24