how to find the column number in vba

[kort]

Hello

i`m trying to find the column number in sheets "STATESTIK" to use as a variable

the problem is variable B

the value in "RESULTAT" "B2" is today date
the dates 01.01.2010, 01.02.2010, 01.03..... is in sheets "STATESTIK2 row "5"
at the først day in a month i want to copy the value in sheets "RÅDATA" "D2" to "STATESTIK" (A, B)

if i define the variable B = f.ex 5 it works

how can tell vba to retun the column number of f.ex 01.01.2010

a = Application.Match(Sheets("RÅDATA").Range("H2"), Sheets("STATESTIK").Range("A1:A54"), 0)
B = Application.Index(Sheets("RESULTAT").Range("B2"), Sheets("STATESTIK").Rows("1:30"), 0)
Sheets("RÅDATA").Range("D2").Copy
Sheets("STATESTIK").Cells(a, B).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

 

[craig.penny]

try this:

MyVariable = sheets("STATESTIK2).rows(5).Find(what:=MyDate, LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False).column

MyVariable will be the column number where MyDate is found in row 5 of "STATESTIK2". You will need to have a value for MyDate before this line runs.

The way this works, is that once you have the cell object (the .find method above returns a cell object here) then you can get the column number with .column.

Like if you have

Dim oCell as object

and at some point you've done

Set oCell =

some cell on your sheet

then

oCell.column

will give you the column number of oCell. It would also work with

selection.column

and

activecell.column

 

[kort]

Hello thanks for the help

this is a bit advanced for me
i tryed this but got a error
run-time error 91
objekt variable or whit blok variable not set???



Sub Makro1()
'
' Makro1 Makro
' copy antall ao i startet z569pon
'


a = Application.Match(Sheets("RÅDATA").Range("H2"), Sheets("STATESTIK").Range("A1:A54"), 0)
B = Sheets("STATESTIK").Rows(5).Find(what:=(Sheets("RESULTAT").Range("B2")), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False).Column
Sheets("RÅDATA").Range("D2").Copy
Sheets("STATESTIK").Cells(a, B).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

 

[wigi]

This will give the same error, but at least it's more clear where to search.

Sub Makro1()
    A = Application.Match([RÅDATA!H2], [STATESTIK!A1:A54], 0)
    B = [STATESTIK!5:5].Find([RESULTAT!B2], , xlValues, xlWhole).Column
    Sheets("STATESTIK").Cells(A, B).Value = [RÅDATA!D2]
End Sub

Does the value of cell B2 on sheet RESULTAT occur in row 5 on sheet STATESTIK?
No typo's? No leading/trailing spaces?
...

Please use code tags when you post code here on the forum. It makes the code readable. Thanks.

 

[kort]

This will give the same error, but at least it's more clear where to search.

Sub Makro1()
    A = Application.Match([RÅDATA!H2], [STATESTIK!A1:A54], 0)
    B = [STATESTIK!5:5].Find([RESULTAT!B2], , xlValues, xlWhole).Column
    Sheets("STATESTIK").Cells(A, B).Value = [RÅDATA!D2]
End Sub

Does the value of cell B2 on sheet RESULTAT occur in row 5 on sheet STATESTIK?
In this test. yes!
( but it vill not always occor. B2 is todays date, the value in row 5 is 01.01.2010,01.02.2010, 01.03...)

No typo's?
i cant find any
..
No leading/trailing spaces?
i have cut and paste ?
...

Please use code tags when you post code here on the forum. It makes the code readable. Thanks.

.

 

[wigi]

Does the value of cell B2 on sheet RESULTAT occur in row 5 on sheet STATESTIK? In this test. yes!

Are they formatted the same way?
Can you find it yourself if you do Ctrl-F? By the way, what is in the search box if you hit Ctrl-F right after the macro?

 

[kort]

Are they formatted the same way?
Can you find it yourself if you do Ctrl-F? By the way, what is in the search box if you hit Ctrl-F right after the macro?

Are they formatted the same way? yes

By the way, what is in the search box if you hit Ctrl-F right after the macro?
where in excel or vba? the macro stops

if a use this code it vil work but only when the valu orrur

code:
Sub Makro5()
'
' Makro1 Makro
' copy antall ao i startet z569pon
'


A = Application.Match(Sheets("RÅDATA").Range("H2"), Sheets("STATESTIK").Range("A1:A54"), 0)
If Sheets("RESULTAT").Range("B2") = ("01.01.2010") Then B = 4
If Sheets("RESULTAT").Range("B2") = ("01.02.2010") Then B = 5
Sheets("RÅDATA").Range("D2").Copy
Sheets("STATESTIK").Cells(A, B).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

 

[craig.penny]

Above where you have

B = Sheets("STATESTIK").Rows(5).Find(what:=(Sheets("RESULTAT").Range("B2")), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False).Column

try this

B = Sheets("STATESTIK").Rows(5).Find(what:=(Sheets("RESULTAT").Range("B2").value), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False).Column

The only difference is the .value - it may not help at all but it's worth a try.

If that doesn't work you can try this

B = Sheets("STATESTIK").Rows(5).Find(what:=(Sheets("RESULTAT").Range("B2").value), LookIn:=xlValues, lookat:=xlpart, MatchCase:=False).Column

The only difference there is I changed xlwhole to xlpart. Hopefully one of those will work for you, if not... I'm out of ideas.

Good Luck!

 

[kort]

hello i`v tryed it all and i get the same error

run-time error 91

OBJEKT VARIABLE OR WHIT BLOCK VARIABLE NOT SET

Sub Makro1()
a = Application.Match(Sheets("RÅDATA").Range("H2"), Sheets("STATESTIK").Range("A1:A54"), 0)
B = Sheets("STATESTIK").Rows(5).Find(what:=(Sheets("RESULTAT").Range("B2").value), LookIn:=xlValues, lookat:=xlWhole, MatchCase:=False).ColumnSheets("RÅDATA").Range("D2").Copy
Sheets("STATESTIK").Cells(a, B).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

OR

Sub Makro1()
    A = Application.Match([RÅDATA!H2], [STATESTIK!A1:A54], 0)
    B = [STATESTIK!5:5].Find([RESULTAT!B2], , xlValues, xlWhole).Column
    Sheets("STATESTIK").Cells(A, B).Value = [RÅDATA!D2]
End Sub

is it posible to do something whit this code to make it run when the value dosent occur

Sub Makro5()
'
' Makro1 Makro
' copy antall ao i startet z569pon
'

 
a = Application.Match(Sheets("RÅDATA").Range("H2"), Sheets("STATESTIK").Range("A1:A54"), 0)
If Sheets("RESULTAT").Range("B2") = ("01.01.2010") Then B = 4
If Sheets("RESULTAT").Range("B2") = ("01.02.2010") Then B = 5
If Sheets("RESULTAT").Range("B2") = ("01.03.2010") Then B = 6
If Sheets("RESULTAT").Range("B2") = ("01.04.2010") Then B = 7
If Sheets("RESULTAT").Range("B2") = ("01.05.2010") Then B = 8
If Sheets("RESULTAT").Range("B2") = ("01.06.2010") Then B = 9
If Sheets("RESULTAT").Range("B2") = ("01.07.2010") Then B = 10
If Sheets("RESULTAT").Range("B2") = ("01.08.2010") Then B = 11
If Sheets("RESULTAT").Range("B2") = ("01.09.2010") Then B = 12
If Sheets("RESULTAT").Range("B2") = ("01.10.2010") Then B = 13
If Sheets("RESULTAT").Range("B2") = ("01.11.2010") Then B = 14
If Sheets("RESULTAT").Range("B2") = ("01.12.2010") Then B = 15
Sheets("RÅDATA").Range("D2").Copy
Sheets("STATESTIK").Cells(a, B).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

 

[kort]

.