• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

compare my array with column headers

smita

New Member
Hi,
I have given to write a query. You might find my post in other forums. None of them solved my question. I hope chandoo forum would help me to get out of this.

I am comparing my array list with column headers from another excel workbook saved in folder c:\folder\excel.xls. if the workbook header doesn't contain any of the values specified in the array then it should write zero;s in my workbook.
My array list has column1,2,3,4 and my excel sheet has headers column1 and column4 only. So the code below should write column2 and column3 with zero;s in it till end.
But the below code writes column1 | column2 | column3 |column4 |column1 | column4 |

please help. It didn't work as i expected.

Code:
Dim v,wb As Workbook
v = Array("column1","column2","column3", "column4")
Application.ScreenUpdating = False
Set wb = Workbooks.Open("C:\folder\excel.xls")
With wb.Sheets("sheetABC")

For x = 0 To UBound(v)
If .Cells(1, x + 1).Value = v(x) Then GoTo nnext
If .Cells(1, x + 1) <> "" Then
.Cells(1, x + 1).EntireColumn.Insert
.Cells(1, x + 1).Value = v(x)
End If
If .Cells(1, x + 1) = "" Then
.Cells(1, x + 1).Value = v(x)
End If
nnext:
Next
.Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks).Value = 0
End With
wb.Save
 
Last edited:

If it's not a lower / upper case problem,
so it's a bad conception ! Check logic of your If statements …

See what happens during code execution
with step by step mode via using F8 key …
 
Iterates these lines again and again. In the first iteration writes column1, 2nd iteration writes column2 &3rd iteration writes column3....

Code:
For x = 0 To UBound(v)
If .Cells(1, x + 1).Value = v(x) Then GoTo nnext
If .Cells(1, x + 1) <> "" Then
.Cells(1, x + 1).EntireColumn.Insert
.Cells(1, x + 1).Value = v(x)
End If
If .Cells(1, x + 1) = "" Then
.Cells(1, x + 1).Value = v(x)

During this line of code it writes zero's shown below

Code:
.Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks).Value = 0
 
Hi Smita ,

Can you explain why you want to do what ever you expect this macro to do ?

If the column headers match , say for the header column1 and column4 , the code does nothing ; what is the purpose of entering zeroes in the columns which have been inserted ?

Since you are not specifying any bounds , how far down the column do you want zeros to be put in ?

Lastly , you need to read up on the property CurrentRegion ; how is this defined , and to what does it refer within your code ? If you are stepping through the code , when you come to the following line :

.Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks).Value = 0

type in the Immediate window :

?wb.Sheets("sheetABC").Range("A1").CurrentRegion.address

and see what is displayed.

Narayan
 
Narayan,


If the column headers match , say for the header column1 and column4 , the code does nothing ; what is the purpose of entering zeroes in the columns which have been inserted ?

What is the purpose?! . The requirement is like this to be designed

Since you are not specifying any bounds , how far down the column do you want zeros to be put in ?

I would like to put zero's for the first 10 rows after column headers means A2:A10

Lastly , you need to read up on the property CurrentRegion ; how is this defined , and to what does it refer within your code ? If you are stepping through the code , when you come to the following line :

.Range("A1").CurrentRegion.SpecialCells(xlCellTypeBlanks).Value = 0

type in the Immediate window :

?wb.Sheets("sheetABC").Range("A1").CurrentRegion.address

and see what is displayed.


Thank you. Would try this.


Hi Smita ,

Can you explain why you want to do what ever you expect this macro to do ?

If the column headers match , say for the header column1 and column4 , the code does nothing ; what is the purpose of entering zeroes in the columns which have been inserted ?

Since you are not specifying any bounds , how far down the column do you want zeros to be put in ?

Narayan
 
Posting the code which satisfies everything for me expect writing zero's till 10th row. would be glad if someone can help me. Posting the code for someone who faces similar situation
Code:
For y = 1 To 150
  For x = LBound(v) To UBound(v)
  If .Cells(1, y).Value = v(x) Then v(x) = ""
  Next
Next
z = 1
For x = LBound(v) To UBound(v)
  If v(x) <> "" Then
  If .Cells(1, z) <> "" Then
  .Cells(1, z).EntireColumn.Insert
  .Cells(1, z).Value = v(x)
  '.Range(.Cells(1, 1), .Cells(10, 10)).Value = 0
  
  End If
  If .Cells(1, z) = "" Then
  .Cells(1, z).Value = v(x)
  End If
  z = z + 1
End if
next
end with
 
complete code..Hope it helps someone!

Code:
For y = 1 To 150
  For x = LBound(v) To UBound(v)
  If .Cells(1, y).Value = v(x) Then v(x) = ""
  Next
Next
z = 1
For x = LBound(v) To UBound(v)
  If v(x) <> "" Then
  If .Cells(1, z) <> "" Then
  .Cells(1, z).EntireColumn.Insert
  .Cells(1, z).Value = v(x)
  '.Range(.Cells(1, 1), .Cells(10, 10)).Value = 0  
  End If
  If .Cells(1, z) = "" Then
  .Cells(1, z).Value = v(x)
  End If
for i=2 to 20
.Cells(iCount, z).Value = 0
  Next
 z = z + 1
End if
next
end with
 
Back
Top