Excel Macros

P

puppy26

New Member
Joined
Feb 23, 2012
Messages
42
Actually I need to write a program in Macros that insert a value if the conditions matches..

say for example,

K(column name) = "BU Accessories" And AG(column name) = "APPAREL" And AM = "CCS" And AO = "Inline" And BL = "new" Then CE = "18 months"
that is IF all the above mentioned conditions matches with corresponding rows, then column CE should be automatically assigned to 18 months.

I tried with this program but i didnt get any value assigned to particular column..

Sub model()

Dim K As String
Dim AG As String
Dim AM As String
Dim AO As String
Dim BL As String
Dim CE As Integer

If K = "BU Accessories" Then
If AG = "Apparel" Then
If AM = "CCS" Then
If AO = "Inline" Then
If BL = "new" Then

CE = 18
Else
CE = 0

End If
End If
End If
End If
End If
End Sub
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
Sort by date Sort by votes
Perhaps
=IF(AND(K2="BU Accessories", AG2="APPAREL", [rest of your conditions]), "18 Months", "")

Does that help?

Regards
Adam
 
Upvote 0
Hi Puppy

Welcome to the board :)

If you want code to do this then you need to approach it slightly differently, I believe this will do what you want...

Code: 
Sub Model()
Application.ScreenUpdating = False

LastRow = Range("K" & Rows.Count).End(xlUp).Row

For i = 1 To LastRow

    If Cells(i, "K").Value = "BU Accessories" And Cells(i, "AG").Value = "Apparel" And Cells(i, "AM").Value = "CCS" And Cells(i, "AO").Value = "Inline" And Cells(i, "BL").Value = "new" Then
        Cells(i, "CE").Value = 18
    End If
       
Next i

Application.StatusBar = ""
Application.ScreenUpdating = True

End Sub

Your code wasn't giving any refences to check really, you can't just say "IF K =" because it doesn't know what K is :)

Does the code make sense to you?
 
Upvote 0
HI.,.
thanks for your code but its not working actually...

Do I need to change\modify anything in that!!!


JazzSP8 said:
Hi Puppy

Welcome to the board :)

If you want code to do this then you need to approach it slightly differently, I believe this will do what you want...

Code: 
Sub Model()
Application.ScreenUpdating = False
 
LastRow = Range("K" & Rows.Count).End(xlUp).Row
 
For i = 1 To LastRow
 
    If Cells(i, "K").Value = "BU Accessories" And Cells(i, "AG").Value = "Apparel" And Cells(i, "AM").Value = "CCS" And Cells(i, "AO").Value = "Inline" And Cells(i, "BL").Value = "new" Then
        Cells(i, "CE").Value = 18
    End If
 
Next i
 
Application.StatusBar = ""
Application.ScreenUpdating = True
 
End Sub

Your code wasn't giving any refences to check really, you can't just say "IF K =" because it doesn't know what K is :)

Does the code make sense to you?
Click to expand...
 
Upvote 0
puppy26 said:
HI.,.
thanks for your code but its not working actually...

Do I need to change\modify anything in that!!!
Click to expand...

Well, yeah .. If it's not working... Which it does according to the criteria you seemed to specifying...

How is it not working? - Is your original criteria correct?

Something to consider is that the critera needs to match 100%, so "New" and "new" aren't the same thing... Also, where did you post the code, I assumed you'd want it in the Sheet code?
 
Upvote 0
that's great.. I thought its not case sensitive...now its work perfectly.. thanks a lot..

so could you please say me where should I include next condition..say for example..

<TABLE style="WIDTH: 686pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=914><COLGROUP><COL style="WIDTH: 181pt; mso-width-source: userset; mso-width-alt: 8813" width=241><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 5229" width=143><COL style="WIDTH: 126pt; mso-width-source: userset; mso-width-alt: 6144" width=168><COL style="WIDTH: 97pt; mso-width-source: userset; mso-width-alt: 4717" width=129><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4754" width=130><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 181pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=17 width=241>BU Accessories</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 107pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 width=143>APPAREL</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 126pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=168>CCT</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 97pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=129>INLINE</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 98pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=130>NEW</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 77pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=103>18
</TD></TR></TBODY></TABLE>


Could you please say..
JazzSP8 said:
Well, yeah .. If it's not working... Which it does according to the criteria you seemed to specifying...

How is it not working? - Is your original criteria correct?

Something to consider is that the critera needs to match 100%, so "New" and "new" aren't the same thing... Also, where did you post the code, I assumed you'd want it in the Sheet code?
Click to expand...
 
Upvote 0
well actually I got the solution for my other questions too..

thanks a lot for your help dude..

puppy26 said:
that's great.. I thought its not case sensitive...now its work perfectly.. thanks a lot..

so could you please say me where should I include next condition..say for example..

<TABLE style="WIDTH: 686pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=914><COLGROUP><COL style="WIDTH: 181pt; mso-width-source: userset; mso-width-alt: 8813" width=241><COL style="WIDTH: 107pt; mso-width-source: userset; mso-width-alt: 5229" width=143><COL style="WIDTH: 126pt; mso-width-source: userset; mso-width-alt: 6144" width=168><COL style="WIDTH: 97pt; mso-width-source: userset; mso-width-alt: 4717" width=129><COL style="WIDTH: 98pt; mso-width-source: userset; mso-width-alt: 4754" width=130><COL style="WIDTH: 77pt; mso-width-source: userset; mso-width-alt: 3766" width=103><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 181pt; HEIGHT: 12.75pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 height=17 width=241>BU Accessories</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 107pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" class=xl65 width=143>APPAREL</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 126pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=168>CCT</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 97pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=129>INLINE</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 98pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=130>NEW</TD><TD style="BORDER-BOTTOM: #d4d0c8; BORDER-LEFT: #d4d0c8; BACKGROUND-COLOR: transparent; WIDTH: 77pt; BORDER-TOP: #d4d0c8; BORDER-RIGHT: #d4d0c8" width=103>18

</TD></TR></TBODY></TABLE>


Could you please say..
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Solved
populating dates of the current month with a formula
Replies
6
Views
108
jbrown021286
J
J
  • Question
adding cells based on criteria across several columns
Replies
5
Views
214
Cubist
Cubist
J
  • Solved
trending value projections without counting sundays
Replies
2
Views
165
KRice
K
S
  • Question
VBA Code - Create Log sheet (Errorlog) in case VBA Macro condition is not met
Replies
1
Views
218
Tupe77
T
gtd526
  • Question
vba selecting a specific range if ISNUMBER
Replies
1
Views
211
gtd526
gtd526

Forum statistics

Threads
1,216,372
Messages
6,130,223
Members
449,567
Latest member
ashsweety

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top