ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)
-   -   Macro Question ?? (https://www.excelbanter.com/excel-worksheet-functions/143175-macro-question.html)

Carl
Macro Question ??
 

My workbook contains 10 worksheets. Each worksheet is identical but
represents a different day.



Each worksheet has a table in in A10:I1000



I am trying to create a new table in J10:O1000 that will do this:



For Col J of the new table, if the value in ColA is equal to A,B,C,E;F;G, or
H (or a list of items I have) and the value in ColD is less than or equal to
..08, return .08, otherwise return the value in ColD



For Col K of the new table, if the value in ColA is equal to A,B,C,E;F;G, or
H and the value in ColD is less than or equal to .08, return ColE minus (.08
divided by 2)



For Col L of the new table, if the value in ColA is equal to A,B,C,E;F;G, or
H and the value in ColD is less than or equal to .08, return ColF minus (.08
divided by 2)



For Col M,N and O, return the values from Col G,H,I respectively.



I would like to perform this operation on all selected worksheets.





Thank you in advance.

joel
Macro Question ??
 
Sub addnewcolumns()

For Each ws In Worksheets
With Sheets(ws.Name)

For RowCount = 10 To 1000

If .Cells(RowCount, "D") 0.08 Then

.Cells(RowCount, "J") = .Cells(RowCount, "D")
Else
If (.Cells(RowCount, "A") = .Cells(RowCount, "B")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "C")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "E")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "F")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "G")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "H")) Then

.Cells(RowCount, "J") = 0.08
Else
.Cells(RowCount, "J") = .Cells(RowCount, "D")

End If

End If

If .Cells(RowCount, "D") <= 0.08 Then
If (.Cells(RowCount, "A") = .Cells(RowCount, "B")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "C")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "D")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "F")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "G")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "H")) Then

.Cells(RowCount, "K") = .Cells(RowCount, "E") - (0.08 / 2)
End If
End If


If .Cells(RowCount, "D") <= 0.08 Then
If (.Cells(RowCount, "A") = .Cells(RowCount, "B")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "C")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "D")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "F")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "G")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "H")) Then

.Cells(RowCount, "L") = .Cells(RowCount, "F") - (0.08 / 2)
End If
End If

.Cells(RowCount, "M") = .Cells(RowCount, "G")
.Cells(RowCount, "N") = .Cells(RowCount, "H")
.Cells(RowCount, "O") = .Cells(RowCount, "I")


Next RowCount

End With

Next ws


End Sub


"carl" wrote:


My workbook contains 10 worksheets. Each worksheet is identical but
represents a different day.



Each worksheet has a table in in A10:I1000



I am trying to create a new table in J10:O1000 that will do this:



For Col J of the new table, if the value in ColA is equal to A,B,C,E;F;G, or
H (or a list of items I have) and the value in ColD is less than or equal to
.08, return .08, otherwise return the value in ColD



For Col K of the new table, if the value in ColA is equal to A,B,C,E;F;G, or
H and the value in ColD is less than or equal to .08, return ColE minus (.08
divided by 2)



For Col L of the new table, if the value in ColA is equal to A,B,C,E;F;G, or
H and the value in ColD is less than or equal to .08, return ColF minus (.08
divided by 2)



For Col M,N and O, return the values from Col G,H,I respectively.



I would like to perform this operation on all selected worksheets.





Thank you in advance.

Carl
Macro Question ??
 
Thank you Joel.

The code got hung up he

Sub addnewcolumns()



For Each ws In Worksheets

With Sheets(ws.Name)



For RowCount = 10 To 1000



If .Cells(RowCount, "D") 0.08 Then



.Cells(RowCount, "J") = .Cells(RowCount, "D")

Else

Code Hung Up He

If (.Cells(RowCount, "A") = .Cells(RowCount, "IWM")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QQQQ")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "SPY")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "AAPL")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "DNDN")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QCOM")) Then



.Cells(RowCount, "O") = 0.08

Else

.Cells(RowCount, "O") = .Cells(RowCount, "D")



End If



End If



If .Cells(RowCount, "D") <= 0.08 Then

If (.Cells(RowCount, "A") = .Cells(RowCount, "IWM")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QQQQ")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "SPY")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "AAPL")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "DNDN")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QCOM")) Then



.Cells(RowCount, "P") = .Cells(RowCount, "E") - (0.08 / 2)

End If

End If





If .Cells(RowCount, "D") <= 0.08 Then

If (.Cells(RowCount, "A") = .Cells(RowCount, "IWM")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QQQQ")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "SPY")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "AAPL")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "DNDN")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QCOM")) Then



.Cells(RowCount, "O") = .Cells(RowCount, "F") - (0.08 / 2)

End If

End If



.Cells(RowCount, "Q") = .Cells(RowCount, "G")

.Cells(RowCount, "R") = .Cells(RowCount, "H")

.Cells(RowCount, "S") = .Cells(RowCount, "I")





Next RowCount



End With



Next ws





End Sub



"Joel" wrote:

Sub addnewcolumns()

For Each ws In Worksheets
With Sheets(ws.Name)

For RowCount = 10 To 1000

If .Cells(RowCount, "D") 0.08 Then

.Cells(RowCount, "J") = .Cells(RowCount, "D")
Else
If (.Cells(RowCount, "A") = .Cells(RowCount, "B")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "C")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "E")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "F")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "G")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "H")) Then

.Cells(RowCount, "J") = 0.08
Else
.Cells(RowCount, "J") = .Cells(RowCount, "D")

End If

End If

If .Cells(RowCount, "D") <= 0.08 Then
If (.Cells(RowCount, "A") = .Cells(RowCount, "B")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "C")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "D")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "F")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "G")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "H")) Then

.Cells(RowCount, "K") = .Cells(RowCount, "E") - (0.08 / 2)
End If
End If


If .Cells(RowCount, "D") <= 0.08 Then
If (.Cells(RowCount, "A") = .Cells(RowCount, "B")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "C")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "D")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "F")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "G")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "H")) Then

.Cells(RowCount, "L") = .Cells(RowCount, "F") - (0.08 / 2)
End If
End If

.Cells(RowCount, "M") = .Cells(RowCount, "G")
.Cells(RowCount, "N") = .Cells(RowCount, "H")
.Cells(RowCount, "O") = .Cells(RowCount, "I")


Next RowCount

End With

Next ws


End Sub


"carl" wrote:


My workbook contains 10 worksheets. Each worksheet is identical but
represents a different day.



Each worksheet has a table in in A10:I1000



I am trying to create a new table in J10:O1000 that will do this:



For Col J of the new table, if the value in ColA is equal to A,B,C,E;F;G, or
H (or a list of items I have) and the value in ColD is less than or equal to
.08, return .08, otherwise return the value in ColD



For Col K of the new table, if the value in ColA is equal to A,B,C,E;F;G, or
H and the value in ColD is less than or equal to .08, return ColE minus (.08
divided by 2)



For Col L of the new table, if the value in ColA is equal to A,B,C,E;F;G, or
H and the value in ColD is less than or equal to .08, return ColF minus (.08
divided by 2)



For Col M,N and O, return the values from Col G,H,I respectively.



I would like to perform this operation on all selected worksheets.





Thank you in advance.

joel
Macro Question ??
 
You don't understand the cells(row,column) format.

Cells(RowCount, "A").value (value optional)
This is the data in cell Column A row 1.

I think you want to change
from
If (.Cells(RowCount, "A") = .Cells(RowCount, "IWM")) Or _

to
If (.Cells(RowCount, "A") = "IWM") Or _


Make similar changes to rest of code.

"carl" wrote:

Thank you Joel.

The code got hung up he

Sub addnewcolumns()



For Each ws In Worksheets

With Sheets(ws.Name)



For RowCount = 10 To 1000



If .Cells(RowCount, "D") 0.08 Then



.Cells(RowCount, "J") = .Cells(RowCount, "D")

Else

Code Hung Up He

If (.Cells(RowCount, "A") = .Cells(RowCount, "IWM")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QQQQ")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "SPY")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "AAPL")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "DNDN")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QCOM")) Then



.Cells(RowCount, "O") = 0.08

Else

.Cells(RowCount, "O") = .Cells(RowCount, "D")



End If



End If



If .Cells(RowCount, "D") <= 0.08 Then

If (.Cells(RowCount, "A") = .Cells(RowCount, "IWM")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QQQQ")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "SPY")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "AAPL")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "DNDN")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QCOM")) Then



.Cells(RowCount, "P") = .Cells(RowCount, "E") - (0.08 / 2)

End If

End If





If .Cells(RowCount, "D") <= 0.08 Then

If (.Cells(RowCount, "A") = .Cells(RowCount, "IWM")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QQQQ")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "SPY")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "AAPL")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "DNDN")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QCOM")) Then



.Cells(RowCount, "O") = .Cells(RowCount, "F") - (0.08 / 2)

End If

End If



.Cells(RowCount, "Q") = .Cells(RowCount, "G")

.Cells(RowCount, "R") = .Cells(RowCount, "H")

.Cells(RowCount, "S") = .Cells(RowCount, "I")





Next RowCount



End With



Next ws





End Sub



"Joel" wrote:

Sub addnewcolumns()

For Each ws In Worksheets
With Sheets(ws.Name)

For RowCount = 10 To 1000

If .Cells(RowCount, "D") 0.08 Then

.Cells(RowCount, "J") = .Cells(RowCount, "D")
Else
If (.Cells(RowCount, "A") = .Cells(RowCount, "B")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "C")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "E")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "F")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "G")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "H")) Then

.Cells(RowCount, "J") = 0.08
Else
.Cells(RowCount, "J") = .Cells(RowCount, "D")

End If

End If

If .Cells(RowCount, "D") <= 0.08 Then
If (.Cells(RowCount, "A") = .Cells(RowCount, "B")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "C")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "D")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "F")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "G")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "H")) Then

.Cells(RowCount, "K") = .Cells(RowCount, "E") - (0.08 / 2)
End If
End If


If .Cells(RowCount, "D") <= 0.08 Then
If (.Cells(RowCount, "A") = .Cells(RowCount, "B")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "C")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "D")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "F")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "G")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "H")) Then

.Cells(RowCount, "L") = .Cells(RowCount, "F") - (0.08 / 2)
End If
End If

.Cells(RowCount, "M") = .Cells(RowCount, "G")
.Cells(RowCount, "N") = .Cells(RowCount, "H")
.Cells(RowCount, "O") = .Cells(RowCount, "I")


Next RowCount

End With

Next ws


End Sub


"carl" wrote:


My workbook contains 10 worksheets. Each worksheet is identical but
represents a different day.



Each worksheet has a table in in A10:I1000



I am trying to create a new table in J10:O1000 that will do this:



For Col J of the new table, if the value in ColA is equal to A,B,C,E;F;G, or
H (or a list of items I have) and the value in ColD is less than or equal to
.08, return .08, otherwise return the value in ColD



For Col K of the new table, if the value in ColA is equal to A,B,C,E;F;G, or
H and the value in ColD is less than or equal to .08, return ColE minus (.08
divided by 2)



For Col L of the new table, if the value in ColA is equal to A,B,C,E;F;G, or
H and the value in ColD is less than or equal to .08, return ColF minus (.08
divided by 2)



For Col M,N and O, return the values from Col G,H,I respectively.



I would like to perform this operation on all selected worksheets.





Thank you in advance.

Carl
Macro Question ??
 
Thank you so much for your help. Its working. Im learning.

3 things I am trying to adjust:


1. If Value in A is not equal to value in €œmy list€ (IWM,QQQQ etc), do this
part


.Cells(RowCount, "M") = .Cells(RowCount, "G")

.Cells(RowCount, "N") = .Cells(RowCount, "H")

.Cells(RowCount, "O") = .Cells(RowCount, "I")



2. For these 2 lines, instead of (0.08 / 2) do this calc - (value in ColA / 2)


.Cells(RowCount, "K") = .Cells(RowCount, "E") - (0.08 / 2)



Cells(RowCount, "L") = .Cells(RowCount, "F") - (0.08 / 2)



3. Is it possible to change the code to only work on worksheets Ive
selected. So if I have 10 worksheets in the workbook but only select 2 of
them, perform the operation on the 2 selected worksheets



Here's my new code:



For Each ws In Worksheets

With Sheets(ws.Name)



For RowCount = 10 To 20



If .Cells(RowCount, "D") 0.08 Then



.Cells(RowCount, "J") = .Cells(RowCount, "D")

Else

If (.Cells(RowCount, "A") = "IWM") Or _

(.Cells(RowCount, "A") = "QQQQ") Or _

(.Cells(RowCount, "A") = "SPY") Or _

(.Cells(RowCount, "A") = "AAPL") Or _

(.Cells(RowCount, "A") = "IBM") Or _

(.Cells(RowCount, "A") = "DNDN") Then



.Cells(RowCount, "J") = 0.08

Else

.Cells(RowCount, "J") = .Cells(RowCount, "D")



End If



End If



If .Cells(RowCount, "D") <= 0.08 Then

If (.Cells(RowCount, "A") = "IWM") Or _

(.Cells(RowCount, "A") = "QQQQ") Or _

(.Cells(RowCount, "A") = "SPY") Or _

(.Cells(RowCount, "A") = "AAPL") Or _

(.Cells(RowCount, "A") = "IBM") Or _

(.Cells(RowCount, "A") = "DNDN") Then



.Cells(RowCount, "K") = .Cells(RowCount, "E") - (0.08 / 2)

End If

End If





If .Cells(RowCount, "D") <= 0.08 Then

If (.Cells(RowCount, "A") = "IWM") Or _

(.Cells(RowCount, "A") = "QQQQ") Or _

(.Cells(RowCount, "A") = "SPY") Or _

(.Cells(RowCount, "A") = "AAPL") Or _

(.Cells(RowCount, "A") = "IBM") Or _

(.Cells(RowCount, "A") = "DNDN") Then



.Cells(RowCount, "L") = .Cells(RowCount, "F") - (0.08 / 2)

End If

End If



.Cells(RowCount, "M") = .Cells(RowCount, "G")

.Cells(RowCount, "N") = .Cells(RowCount, "H")

.Cells(RowCount, "O") = .Cells(RowCount, "I")





Next RowCount



End With



Next ws







"Joel" wrote:

You don't understand the cells(row,column) format.

Cells(RowCount, "A").value (value optional)
This is the data in cell Column A row 1.

I think you want to change
from
If (.Cells(RowCount, "A") = .Cells(RowCount, "IWM")) Or _

to
If (.Cells(RowCount, "A") = "IWM") Or _


Make similar changes to rest of code.

"carl" wrote:

Thank you Joel.

The code got hung up he

Sub addnewcolumns()



For Each ws In Worksheets

With Sheets(ws.Name)



For RowCount = 10 To 1000



If .Cells(RowCount, "D") 0.08 Then



.Cells(RowCount, "J") = .Cells(RowCount, "D")

Else

Code Hung Up He

If (.Cells(RowCount, "A") = .Cells(RowCount, "IWM")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QQQQ")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "SPY")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "AAPL")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "DNDN")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QCOM")) Then



.Cells(RowCount, "O") = 0.08

Else

.Cells(RowCount, "O") = .Cells(RowCount, "D")



End If



End If



If .Cells(RowCount, "D") <= 0.08 Then

If (.Cells(RowCount, "A") = .Cells(RowCount, "IWM")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QQQQ")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "SPY")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "AAPL")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "DNDN")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QCOM")) Then



.Cells(RowCount, "P") = .Cells(RowCount, "E") - (0.08 / 2)

End If

End If





If .Cells(RowCount, "D") <= 0.08 Then

If (.Cells(RowCount, "A") = .Cells(RowCount, "IWM")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QQQQ")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "SPY")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "AAPL")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "DNDN")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QCOM")) Then



.Cells(RowCount, "O") = .Cells(RowCount, "F") - (0.08 / 2)

End If

End If



.Cells(RowCount, "Q") = .Cells(RowCount, "G")

.Cells(RowCount, "R") = .Cells(RowCount, "H")

.Cells(RowCount, "S") = .Cells(RowCount, "I")





Next RowCount



End With



Next ws





End Sub



"Joel" wrote:

Sub addnewcolumns()

For Each ws In Worksheets
With Sheets(ws.Name)

For RowCount = 10 To 1000

If .Cells(RowCount, "D") 0.08 Then

.Cells(RowCount, "J") = .Cells(RowCount, "D")
Else
If (.Cells(RowCount, "A") = .Cells(RowCount, "B")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "C")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "E")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "F")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "G")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "H")) Then

.Cells(RowCount, "J") = 0.08
Else
.Cells(RowCount, "J") = .Cells(RowCount, "D")

End If

End If

If .Cells(RowCount, "D") <= 0.08 Then
If (.Cells(RowCount, "A") = .Cells(RowCount, "B")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "C")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "D")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "F")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "G")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "H")) Then

.Cells(RowCount, "K") = .Cells(RowCount, "E") - (0.08 / 2)
End If
End If


If .Cells(RowCount, "D") <= 0.08 Then
If (.Cells(RowCount, "A") = .Cells(RowCount, "B")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "C")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "D")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "F")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "G")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "H")) Then

.Cells(RowCount, "L") = .Cells(RowCount, "F") - (0.08 / 2)
End If
End If

.Cells(RowCount, "M") = .Cells(RowCount, "G")
.Cells(RowCount, "N") = .Cells(RowCount, "H")
.Cells(RowCount, "O") = .Cells(RowCount, "I")


Next RowCount

End With

Next ws


End Sub


"carl" wrote:


My workbook contains 10 worksheets. Each worksheet is identical but
represents a different day.



Each worksheet has a table in in A10:I1000



I am trying to create a new table in J10:O1000 that will do this:



For Col J of the new table, if the value in ColA is equal to A,B,C,E;F;G, or
H (or a list of items I have) and the value in ColD is less than or equal to
.08, return .08, otherwise return the value in ColD



For Col K of the new table, if the value in ColA is equal to A,B,C,E;F;G, or
H and the value in ColD is less than or equal to .08, return ColE minus (.08
divided by 2)



For Col L of the new table, if the value in ColA is equal to A,B,C,E;F;G, or
H and the value in ColD is less than or equal to .08, return ColF minus (.08
divided by 2)



For Col M,N and O, return the values from Col G,H,I respectively.



I would like to perform this operation on all selected worksheets.





Thank you in advance.

Carl
Macro Question ??
 
Hi Joel. Please diregard my last reply. I have something wrong in it. I'll
send a new one shortly. Sorry

"carl" wrote:

Thank you so much for your help. Its working. Im learning.

3 things I am trying to adjust:


1. If Value in A is not equal to value in €œmy list€ (IWM,QQQQ etc), do this
part


.Cells(RowCount, "M") = .Cells(RowCount, "G")

.Cells(RowCount, "N") = .Cells(RowCount, "H")

.Cells(RowCount, "O") = .Cells(RowCount, "I")



2. For these 2 lines, instead of (0.08 / 2) do this calc - (value in ColA / 2)


.Cells(RowCount, "K") = .Cells(RowCount, "E") - (0.08 / 2)



Cells(RowCount, "L") = .Cells(RowCount, "F") - (0.08 / 2)



3. Is it possible to change the code to only work on worksheets Ive
selected. So if I have 10 worksheets in the workbook but only select 2 of
them, perform the operation on the 2 selected worksheets



Here's my new code:



For Each ws In Worksheets

With Sheets(ws.Name)



For RowCount = 10 To 20



If .Cells(RowCount, "D") 0.08 Then



.Cells(RowCount, "J") = .Cells(RowCount, "D")

Else

If (.Cells(RowCount, "A") = "IWM") Or _

(.Cells(RowCount, "A") = "QQQQ") Or _

(.Cells(RowCount, "A") = "SPY") Or _

(.Cells(RowCount, "A") = "AAPL") Or _

(.Cells(RowCount, "A") = "IBM") Or _

(.Cells(RowCount, "A") = "DNDN") Then



.Cells(RowCount, "J") = 0.08

Else

.Cells(RowCount, "J") = .Cells(RowCount, "D")



End If



End If



If .Cells(RowCount, "D") <= 0.08 Then

If (.Cells(RowCount, "A") = "IWM") Or _

(.Cells(RowCount, "A") = "QQQQ") Or _

(.Cells(RowCount, "A") = "SPY") Or _

(.Cells(RowCount, "A") = "AAPL") Or _

(.Cells(RowCount, "A") = "IBM") Or _

(.Cells(RowCount, "A") = "DNDN") Then



.Cells(RowCount, "K") = .Cells(RowCount, "E") - (0.08 / 2)

End If

End If





If .Cells(RowCount, "D") <= 0.08 Then

If (.Cells(RowCount, "A") = "IWM") Or _

(.Cells(RowCount, "A") = "QQQQ") Or _

(.Cells(RowCount, "A") = "SPY") Or _

(.Cells(RowCount, "A") = "AAPL") Or _

(.Cells(RowCount, "A") = "IBM") Or _

(.Cells(RowCount, "A") = "DNDN") Then



.Cells(RowCount, "L") = .Cells(RowCount, "F") - (0.08 / 2)

End If

End If



.Cells(RowCount, "M") = .Cells(RowCount, "G")

.Cells(RowCount, "N") = .Cells(RowCount, "H")

.Cells(RowCount, "O") = .Cells(RowCount, "I")





Next RowCount



End With



Next ws







"Joel" wrote:

You don't understand the cells(row,column) format.

Cells(RowCount, "A").value (value optional)
This is the data in cell Column A row 1.

I think you want to change
from
If (.Cells(RowCount, "A") = .Cells(RowCount, "IWM")) Or _

to
If (.Cells(RowCount, "A") = "IWM") Or _


Make similar changes to rest of code.

"carl" wrote:

Thank you Joel.

The code got hung up he

Sub addnewcolumns()



For Each ws In Worksheets

With Sheets(ws.Name)



For RowCount = 10 To 1000



If .Cells(RowCount, "D") 0.08 Then



.Cells(RowCount, "J") = .Cells(RowCount, "D")

Else

Code Hung Up He

If (.Cells(RowCount, "A") = .Cells(RowCount, "IWM")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QQQQ")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "SPY")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "AAPL")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "DNDN")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QCOM")) Then



.Cells(RowCount, "O") = 0.08

Else

.Cells(RowCount, "O") = .Cells(RowCount, "D")



End If



End If



If .Cells(RowCount, "D") <= 0.08 Then

If (.Cells(RowCount, "A") = .Cells(RowCount, "IWM")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QQQQ")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "SPY")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "AAPL")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "DNDN")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QCOM")) Then



.Cells(RowCount, "P") = .Cells(RowCount, "E") - (0.08 / 2)

End If

End If





If .Cells(RowCount, "D") <= 0.08 Then

If (.Cells(RowCount, "A") = .Cells(RowCount, "IWM")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QQQQ")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "SPY")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "AAPL")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "DNDN")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QCOM")) Then



.Cells(RowCount, "O") = .Cells(RowCount, "F") - (0.08 / 2)

End If

End If



.Cells(RowCount, "Q") = .Cells(RowCount, "G")

.Cells(RowCount, "R") = .Cells(RowCount, "H")

.Cells(RowCount, "S") = .Cells(RowCount, "I")





Next RowCount

Carl
Macro Question ??
 
Thanks again. I'm learning. I am trying to make a few changes. Was hoping you
could help.

1. If value in ColA is .08, would like to add the following: take the value
in Col E and display in Col K. Likewise for the value in Col F, if the COLA
is .08, take the value in ColF and display in ColL


2 .For this part:


.Cells(RowCount, "K") = .Cells(RowCount, "E") - (0.08 / 2).
Instead of €œ(0.08 / 2)€ do this (Value in ColD / 2)

3. Is it possible to run the operation on only worksheets Ive selected
€“ versus all worksheets in the workbook.

Thanks Again

Here's the code I am currently running.



Sub addnewcolumns()



For Each ws In Worksheets

With Sheets(ws.Name)



For RowCount = 10 To 20



If .Cells(RowCount, "D") 0.08 Then



.Cells(RowCount, "J") = .Cells(RowCount, "D")

Else

If (.Cells(RowCount, "A") = "IWM") Or _

(.Cells(RowCount, "A") = "QQQQ") Or _

(.Cells(RowCount, "A") = "SPY") Or _

(.Cells(RowCount, "A") = "AAPL") Or _

(.Cells(RowCount, "A") = "IBM") Or _

(.Cells(RowCount, "A") = "DNDN") Then



.Cells(RowCount, "J") = 0.08

Else

.Cells(RowCount, "J") = .Cells(RowCount, "D")



End If



End If



If .Cells(RowCount, "D") <= 0.08 Then

If (.Cells(RowCount, "A") = "IWM") Or _

(.Cells(RowCount, "A") = "QQQQ") Or _

(.Cells(RowCount, "A") = "SPY") Or _

(.Cells(RowCount, "A") = "AAPL") Or _

(.Cells(RowCount, "A") = "IBM") Or _

(.Cells(RowCount, "A") = "DNDN") Then



.Cells(RowCount, "K") = .Cells(RowCount, "E") - (0.08 / 2)

End If

End If





If .Cells(RowCount, "D") <= 0.08 Then

If (.Cells(RowCount, "A") = "IWM") Or _

(.Cells(RowCount, "A") = "QQQQ") Or _

(.Cells(RowCount, "A") = "SPY") Or _

(.Cells(RowCount, "A") = "AAPL") Or _

(.Cells(RowCount, "A") = "IBM") Or _

(.Cells(RowCount, "A") = "DNDN") Then



.Cells(RowCount, "L") = .Cells(RowCount, "F") - (0.08 / 2)

End If

End If



.Cells(RowCount, "M") = .Cells(RowCount, "G")

.Cells(RowCount, "N") = .Cells(RowCount, "H")

.Cells(RowCount, "O") = .Cells(RowCount, "I")





Next RowCount



End With



Next ws







"Joel" wrote:

You don't understand the cells(row,column) format.

Cells(RowCount, "A").value (value optional)
This is the data in cell Column A row 1.

I think you want to change
from
If (.Cells(RowCount, "A") = .Cells(RowCount, "IWM")) Or _

to
If (.Cells(RowCount, "A") = "IWM") Or _


Make similar changes to rest of code.

"carl" wrote:

Thank you Joel.

The code got hung up he

Sub addnewcolumns()



For Each ws In Worksheets

With Sheets(ws.Name)



For RowCount = 10 To 1000



If .Cells(RowCount, "D") 0.08 Then



.Cells(RowCount, "J") = .Cells(RowCount, "D")

Else

Code Hung Up He

If (.Cells(RowCount, "A") = .Cells(RowCount, "IWM")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QQQQ")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "SPY")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "AAPL")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "DNDN")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QCOM")) Then



.Cells(RowCount, "O") = 0.08

Else

.Cells(RowCount, "O") = .Cells(RowCount, "D")



End If



End If



If .Cells(RowCount, "D") <= 0.08 Then

If (.Cells(RowCount, "A") = .Cells(RowCount, "IWM")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QQQQ")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "SPY")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "AAPL")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "DNDN")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QCOM")) Then



.Cells(RowCount, "P") = .Cells(RowCount, "E") - (0.08 / 2)

End If

End If





If .Cells(RowCount, "D") <= 0.08 Then

If (.Cells(RowCount, "A") = .Cells(RowCount, "IWM")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QQQQ")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "SPY")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "AAPL")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "DNDN")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QCOM")) Then



.Cells(RowCount, "O") = .Cells(RowCount, "F") - (0.08 / 2)

End If

End If



.Cells(RowCount, "Q") = .Cells(RowCount, "G")

.Cells(RowCount, "R") = .Cells(RowCount, "H")

.Cells(RowCount, "S") = .Cells(RowCount, "I")





Next RowCount



End With



Next ws





End Sub



"Joel" wrote:

Sub addnewcolumns()

For Each ws In Worksheets
With Sheets(ws.Name)

For RowCount = 10 To 1000

If .Cells(RowCount, "D") 0.08 Then

.Cells(RowCount, "J") = .Cells(RowCount, "D")
Else
If (.Cells(RowCount, "A") = .Cells(RowCount, "B")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "C")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "E")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "F")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "G")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "H")) Then

.Cells(RowCount, "J") = 0.08
Else
.Cells(RowCount, "J") = .Cells(RowCount, "D")

End If

End If

If .Cells(RowCount, "D") <= 0.08 Then
If (.Cells(RowCount, "A") = .Cells(RowCount, "B")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "C")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "D")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "F")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "G")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "H")) Then

.Cells(RowCount, "K") = .Cells(RowCount, "E") - (0.08 / 2)
End If
End If


If .Cells(RowCount, "D") <= 0.08 Then
If (.Cells(RowCount, "A") = .Cells(RowCount, "B")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "C")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "D")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "F")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "G")) Or _
(.Cells(RowCount, "A") = .Cells(RowCount, "H")) Then

.Cells(RowCount, "L") = .Cells(RowCount, "F") - (0.08 / 2)
End If
End If

.Cells(RowCount, "M") = .Cells(RowCount, "G")
.Cells(RowCount, "N") = .Cells(RowCount, "H")
.Cells(RowCount, "O") = .Cells(RowCount, "I")


Next RowCount

End With

Next ws


End Sub


"carl" wrote:


My workbook contains 10 worksheets. Each worksheet is identical but
represents a different day.



Each worksheet has a table in in A10:I1000



I am trying to create a new table in J10:O1000 that will do this:



For Col J of the new table, if the value in ColA is equal to A,B,C,E;F;G, or
H (or a list of items I have) and the value in ColD is less than or equal to
.08, return .08, otherwise return the value in ColD



For Col K of the new table, if the value in ColA is equal to A,B,C,E;F;G, or
H and the value in ColD is less than or equal to .08, return ColE minus (.08
divided by 2)



For Col L of the new table, if the value in ColA is equal to A,B,C,E;F;G, or
H and the value in ColD is less than or equal to .08, return ColF minus (.08
divided by 2)



For Col M,N and O, return the values from Col G,H,I respectively.



I would like to perform this operation on all selected worksheets.





Thank you in advance.

joel
Macro Question ??
 
Sub addnewcolumns()


' Note I commented out statements by putting ' in front
'For Each ws In Worksheets

' With Sheets(ws.Name)
'new code
With Sheets(ActiveSheet.Name)



For RowCount = 10 To 20



If .Cells(RowCount, "D") 0.08 Then



.Cells(RowCount, "J") = .Cells(RowCount, "D")

Else
If (.Cells(RowCount, "A") = "IWM") Or _
(.Cells(RowCount, "A") = "QQQQ") Or _
(.Cells(RowCount, "A") = "SPY") Or _
(.Cells(RowCount, "A") = "AAPL") Or _
(.Cells(RowCount, "A") = "IBM") Or _
(.Cells(RowCount, "A") = "DNDN") Then

.Cells(RowCount, "J") = 0.08

Else

.Cells(RowCount, "J") = .Cells(RowCount, "D")

End If

End If

If .Cells(RowCount, "A") 0.08 Then

.Cells(RowCount, "K") = .Cells(RowCount, "E")

Else

If (.Cells(RowCount, "A") = "IWM") Or _
(.Cells(RowCount, "A") = "QQQQ") Or _
(.Cells(RowCount, "A") = "SPY") Or _
(.Cells(RowCount, "A") = "AAPL") Or _
(.Cells(RowCount, "A") = "IBM") Or _
(.Cells(RowCount, "A") = "DNDN") Then

.Cells(RowCount, "K") = _
.Cells(RowCount, "E") - _
(.Cells(RowCount, "D") / 2)

End If

End If

If .Cells(RowCount, "A") 0.08 Then

.Cells(RowCount, "L") = .Cells(RowCount, "F")

Else

If (.Cells(RowCount, "A") = "IWM") Or _
(.Cells(RowCount, "A") = "QQQQ") Or _
(.Cells(RowCount, "A") = "SPY") Or _
(.Cells(RowCount, "A") = "AAPL") Or _
(.Cells(RowCount, "A") = "IBM") Or _
(.Cells(RowCount, "A") = "DNDN") Then

.Cells(RowCount, "L") = _
.Cells(RowCount, "F") - (0.08 / 2)
End If

End If

.Cells(RowCount, "M") = .Cells(RowCount, "G")
.Cells(RowCount, "N") = .Cells(RowCount, "H")
.Cells(RowCount, "O") = .Cells(RowCount, "I")

Next RowCount

End With

' The next line is also commented out
'Next ws
End Sub


"carl" wrote:

Thanks again. I'm learning. I am trying to make a few changes. Was hoping you
could help.

1. If value in ColA is .08, would like to add the following: take the value
in Col E and display in Col K. Likewise for the value in Col F, if the COLA
is .08, take the value in ColF and display in ColL


2 .For this part:


.Cells(RowCount, "K") = .Cells(RowCount, "E") - (0.08 / 2).
Instead of €œ(0.08 / 2)€ do this (Value in ColD / 2)

3. Is it possible to run the operation on only worksheets Ive selected
€“ versus all worksheets in the workbook.

Thanks Again

Here's the code I am currently running.



Sub addnewcolumns()



For Each ws In Worksheets

With Sheets(ws.Name)



For RowCount = 10 To 20



If .Cells(RowCount, "D") 0.08 Then



.Cells(RowCount, "J") = .Cells(RowCount, "D")

Else

If (.Cells(RowCount, "A") = "IWM") Or _

(.Cells(RowCount, "A") = "QQQQ") Or _

(.Cells(RowCount, "A") = "SPY") Or _

(.Cells(RowCount, "A") = "AAPL") Or _

(.Cells(RowCount, "A") = "IBM") Or _

(.Cells(RowCount, "A") = "DNDN") Then



.Cells(RowCount, "J") = 0.08

Else

.Cells(RowCount, "J") = .Cells(RowCount, "D")



End If



End If



If .Cells(RowCount, "D") <= 0.08 Then

If (.Cells(RowCount, "A") = "IWM") Or _

(.Cells(RowCount, "A") = "QQQQ") Or _

(.Cells(RowCount, "A") = "SPY") Or _

(.Cells(RowCount, "A") = "AAPL") Or _

(.Cells(RowCount, "A") = "IBM") Or _

(.Cells(RowCount, "A") = "DNDN") Then



.Cells(RowCount, "K") = .Cells(RowCount, "E") - (0.08 / 2)

End If

End If





If .Cells(RowCount, "D") <= 0.08 Then

If (.Cells(RowCount, "A") = "IWM") Or _

(.Cells(RowCount, "A") = "QQQQ") Or _

(.Cells(RowCount, "A") = "SPY") Or _

(.Cells(RowCount, "A") = "AAPL") Or _

(.Cells(RowCount, "A") = "IBM") Or _

(.Cells(RowCount, "A") = "DNDN") Then



.Cells(RowCount, "L") = .Cells(RowCount, "F") - (0.08 / 2)

End If

End If



.Cells(RowCount, "M") = .Cells(RowCount, "G")

.Cells(RowCount, "N") = .Cells(RowCount, "H")

.Cells(RowCount, "O") = .Cells(RowCount, "I")





Next RowCount



End With



Next ws







"Joel" wrote:

You don't understand the cells(row,column) format.

Cells(RowCount, "A").value (value optional)
This is the data in cell Column A row 1.

I think you want to change
from
If (.Cells(RowCount, "A") = .Cells(RowCount, "IWM")) Or _

to
If (.Cells(RowCount, "A") = "IWM") Or _


Make similar changes to rest of code.

"carl" wrote:

Thank you Joel.

The code got hung up he

Sub addnewcolumns()



For Each ws In Worksheets

With Sheets(ws.Name)



For RowCount = 10 To 1000



If .Cells(RowCount, "D") 0.08 Then



.Cells(RowCount, "J") = .Cells(RowCount, "D")

Else

Code Hung Up He

If (.Cells(RowCount, "A") = .Cells(RowCount, "IWM")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QQQQ")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "SPY")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "AAPL")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "DNDN")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QCOM")) Then



.Cells(RowCount, "O") = 0.08

Else

.Cells(RowCount, "O") = .Cells(RowCount, "D")



End If



End If



If .Cells(RowCount, "D") <= 0.08 Then

If (.Cells(RowCount, "A") = .Cells(RowCount, "IWM")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QQQQ")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "SPY")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "AAPL")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "DNDN")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QCOM")) Then



.Cells(RowCount, "P") = .Cells(RowCount, "E") - (0.08 / 2)

End If

End If





If .Cells(RowCount, "D") <= 0.08 Then

If (.Cells(RowCount, "A") = .Cells(RowCount, "IWM")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QQQQ")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "SPY")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "AAPL")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "DNDN")) Or _

(.Cells(RowCount, "A") = .Cells(RowCount, "QCOM")) Then



.Cells(RowCount, "O") = .Cells(RowCount, "F") - (0.08 / 2)

End If

End If



.Cells(RowCount, "Q") = .Cells(RowCount, "G")

.Cells(RowCount, "R") = .Cells(RowCount, "H")

.Cells(RowCount, "S") = .Cells(RowCount, "I")





Next RowCount



End With



Next ws


All times are GMT +1. The time now is 02:19 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com