Very complicated macro

I have no idea how I can do this.

I have a table of data. I need to be able to delete data within a particular comlumn with a macro. I also need to be able to create new columns

Also I have to be able to add data into the columns.



thanks in advance

Hi,

As always with XL ... it is all feasible ...
but your request is quite vague ...
Could you come up with more precisions ...?

HTH
Carim

Lets say I have 3 columns with data in them.

I need excel to find the correct column and delete the data when I tell it to.

I also need to add data to the correct column when I tell it to.

This is easy for me. The trick part is being able to add new columns where I do not lose any of the previouse functionality.

For example I might want to delete the informationfrom column 2 and put in some differant data.

Or I might want to Add a new column and add some new data.


Is this easier to undersatnd?

Your discriptions a basic

We understand what you require but to be able to give a better answer to your query we need to know more.

Do you know VBA so that if we give you some general code that you can modify to suit your needs
or do you someone to give you exactly what you require, if it is this option then you need to give exact details of what you require.
eg how will you decide what column needs to be deleted or what column needs to be inserted. This type of info is required so that it can be built into the macro

this image shows what I mean.

above each column is a yes/no drop down box which allows the user to decide whether or not this column will have its data deleted

When using a loops you need a command to start the loop and a command at the end of the loop

using a For command needs a Next command at the end

For myCounter = 1 to 10 step 1
my code here
next myCounter


for commands can also work in reverse
For myCounter = 1 to 10 step -1
my code here
next myCounter

In your posted code you do not have a Next command.
You also need to use numbers or variable in the For command. As you have not declared any variables VBA will have declared b & z as variables with a value of 0

Also when deleting Rows or Columns it is best if you work in reverse because when you delete column C, column D becomes the new column C

I will not actually be deleting columns. I will be deleting the data in them.

You say that it will recognise "b" as the value of 0. I need to be able to have it go through the alphabet letter by letter: so it can go through each column.

Apart from that would this work?

Sub delete_columns()

For y = b To z Step 1

If y("1") = yes Then
Range(y("2"), y("500")).Select
Else: GoTo second
End If

Next y

second:
End Sub

oh and If I try to run it a t the moment it says: "runtime error '13' type mismatch"

Thanks

You will need to use the column numbers

A= column 1
B = Column 2
z= column 26

You will need to use
for y = 2 to 26 step 1

and instead of range("b1").value
try
cell(1,y).value where y is your For variable

Is this what you mean?

Sub delete_columns1()

b = column2
c = column3
d = column4
e = column5
f = column6
g = column7
h = column8
i = column9
j = column10


For y = b To j Step 1

If y("1") = yes Then
Range((cell(y, 1).Value), cell(y, 19).Value).ClearContents
Else:
End If

Next y

second:
End Sub

now whevever I try and run it I get the message:

"complie error sub or function not defined"
and it sellects the word cell.

I'm learning...

Sorry Cell should have been Cells

This is what I meant

Dim y As Integer
For y = 2 To 26 Step 1
If Cells(1, y).Value = "yes" Then
'clear cells in the column that coresponds to the number that y equals for 'rows 2 to 19
Range((Cells(2, y).Address), Cells(19, y).Address).ClearContents
Else:
End If
Next y

When y = 2 the macro is looking at the 2nd column which is B
when y = 13 the macro is looking at the 13th column which is M
when y = 26 the macro is looking at the 26th column which is Z

You can check this by entering this formula in a cell =Column()
or you can change the view of your column letters to numbers by going to the Tools Menu > Options > General Tab & selecting R1C1 reference style.

Also remember when testing using the If command it is cas sensitive yes is not the same as Yes.
You may need to use
Ucase or Lcase or Proper commands as well
for example
If UCase(Cells(1, y).Value) = "YES" Then