Maco help needed !

eddy

Well-known Member
Joined
Mar 2, 2002
Messages
521
I could do with some help scripting a macro.

This is what I would like to do:-
I have a sheet with Product P/Nos in Col C Rows (13 to 39)
I have a VAT figure, in Col F Rows (13 to 39) for each item.
If certain P/Nos are listed in Col C then I want certain other P/Nos to have the VAT figure set to 0.

e.g If P/Nos SOFTEX100, OR SOFTEX200, OR SOFTEX300 (list may grow) are listed THEN IF EITHER NOTMAX400, OR NOTMAX500, OR NOTMAX600 (list may grow), are listed THEN change the VAT of these items to 0

All help would be appreciated
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
Eddy,
Does the VAT Column have existing data in it? So if one of the conditions evaluates to True, you will need to overwrite the data? Would it be OK to hide the VAT column, and create a linked VAT column with a formula? or dod you need to be able to enter data manually in the VAT column? The reason I'm asking, is to determine if you will need a formula or a macro.
 

eddy

Well-known Member
Joined
Mar 2, 2002
Messages
521
Hi ..

Thanks for the reply.
It has to be a macro as I am creating the output from another macro, so I was hoping to just get the new macro to look down the columns and alter the data. The VAT column will not have any data in it until it recieves the value from my macro e.g Prod Value is £10 so the VAT will be £1.75 (=17.5% of the product value), my macro iserts all these values automatically. I then need another macro to zero the VAT if the conditions listed in the previous message apply.

Thanks Ted
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
eddy said:
I could do with some help scripting a macro.

This is what I would like to do:-
I have a sheet with Product P/Nos in Col C Rows (13 to 39)
I have a VAT figure, in Col F Rows (13 to 39) for each item.
If certain P/Nos are listed in Col C then I want certain other P/Nos to have the VAT figure set to 0.

e.g If P/Nos SOFTEX100, OR SOFTEX200, OR SOFTEX300 (list may grow) are listed THEN IF EITHER NOTMAX400, OR NOTMAX500, OR NOTMAX600 (list may grow), are listed THEN change the VAT of these items to 0

All help would be appreciated

So, if any one of Softex100 or Softex200 or Softex300 are found in column C, then NotMAX400, NotMAX500 and NOTMAX600 VAT Values should be 0? With Room for expansion of both lists.
Correct?
 

eddy

Well-known Member
Joined
Mar 2, 2002
Messages
521

ADVERTISEMENT

Yes, thats it in a nutshell. Even though they will all initially have a VAT monetary value. It would be really great if I could have a "Look Up" area
on the sheet and simply add P/Nos to the Look Up table.
e.g say in Col W has Softex100, Softex200,Softex300 (I simply add to this list if I need more), and in Col X has NotMAX400, NotMAX500 and NOTMAX600 (plus I can others to list if I need to).

Thanks Ted
 

eddy

Well-known Member
Joined
Mar 2, 2002
Messages
521
Yes, thats it in a nutshell. Even though they will all initially have a VAT monetary value. It would be really great if I could have a "Look Up" area
on the sheet and simply add P/Nos to the Look Up table.
e.g say in Col W has Softex100, Softex200,Softex300 (I simply add to this list if I need more), and in Col X has NotMAX400, NotMAX500 and NOTMAX600 (plus I can add others to list if I need to).

Thanks Ted
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196

ADVERTISEMENT

You will also need to setup a worksheet called condition in your workbook. The condition worksheet will need to have the following headings.

Column A=Condition 1
Column B=Condition 2

Under Column A put your
SOFTEX100
SOFTEX200
SOFTEX300

Under column B put your
NotMAX400
NotMAX500
NotMAX600

You can add to these lists, and they will be incorperated into the macro.
I have hard coded the range as C13:C39, so if that changes, you will need to update the values.

Add this code to a button

Private Sub CommandButton1_Click()
Dim wb As Workbook, ws As Worksheet, Cond As Worksheet
Dim cell1 As Range, cell2 As Range, cell3 As Range, cell4 As Range

Set wb = ActiveWorkbook
Set ws = ActiveSheet
Set Cond = wb.Worksheets("Conditions")

For Each cell1 In ws.Range("C13:C39")
For Each cell2 In Cond.Range("A2", Cond.Range("a65000").End(xlUp))
If cell1 = cell2 Then
For Each cell3 In Cond.Range("B2", Cond.Range("B65000").End(xlUp))
For Each cell4 In ws.Range("C13:C39")
If cell3 = cell4 Then
cell4.Offset(0, 3).Value = 0
End If
Next
Next
End If
Next
Next
End Sub

Hope this helps.
 

eddy

Well-known Member
Joined
Mar 2, 2002
Messages
521
Brilliant !

The code and lookup table work fine. It does just what I need. I just need to do a bit of fine tuning this end. I need to have the lookup table on my main worksheet called CopySheet . Then I need to enter the Condition 1 data in Column N and the Condition 2 data in Column P. All the other data (columns and rows are the same as before), not too sure of the offsets at the moment, how does this work when going to the left instead the right to insert the 0 value. Can you have negative offsets?
Just one little bug in the code :-
Set Cond = wb.Worksheets("Conditions")
should be Set Cond = wb.Worksheets("Condition")
I really cant thank you enough for helping me with this one, it's been teasing me for ages. I do really appreciate your help.
Regards Ted
 

eddy

Well-known Member
Joined
Mar 2, 2002
Messages
521
Sorry for bothering you, of course all the offsets are still the same.
It work fine.
Thanks again, one really happy customer!
Eddy
 

Cbrine

Well-known Member
Joined
Dec 2, 2003
Messages
3,196
Thanks Ted,
Glad to be appreciated. And yes you can use a negitive number as the offset value, and it will move backwards. Sorry about the typo. I actually didn't make the mistake in the code(I actually built the sheet), I made the mistake in the text telling you to build the condition worksheet instead of the conditions worksheet. Oh well, you figured it out anyway.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,322
Messages
5,769,446
Members
425,546
Latest member
DisMissive

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
Top