Increase column letter

[Azban65]

Hello,

I like to increase the column letter or number in VBA. But none of the code I tried work.

I like to do:

For i = 15 To 196
For j = 15 To 196
if Workbooks(Myname2).Sheets("training").Range(i & "15")= Workbooks(Myname2.Sheets("training").Range(j & "15") Then

Workbooks(Myname2).Sheets("training").Range(i & "15", i & opend).copy

Workbooks(Myname).Sheets("training").Range(j & "15", j & opend).paste

end if

Next
Next

I think you understood that the i and j represent column O to GW. So How can i write that?

Many thanks

Thomas

 

[TonyJollans]

The Cells property takes a numeric row and column number --- [blue][tt]...Sheets("training").Cells(15,i)[/tt][/blue], etc.

Enjoy,
Tony

------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

I'm working (slowly) on my own website

 

[Azban65]

Hi tony,

Many thanks!!!
it works!

 

[SkipVought]

...and

iCol = Sheets("training").Cells(15,"AA").Column

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Azban65]

Hi sorry,
It's still about the same problem.
I need to select a range, I don't know how to do it with the column number andthe cells property.
So I use

when i>=27
columni = Chr(Int(i / 26) + 64) & Chr((i Mod 26) + 64)

But I got an error when I reach AB I got A@ instead... I can't find the correct formula..
Any helps?

 

[SkipVought]

What Tony was pointing out is that you do not need letters to references ranges.

First please explain what your initial parameters are and what range you need to reference, given those initital paarameters.

Check out the Range property...

Set MyRange = Range(Cells(FromRow, FromCol), Cells(ThruRow, ThruCol))

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Azban65]

I don't know how to use this property correctly with my coding.
The fact that I'm working on 2 windows seems to be a problem here.

Here is the code:
Set MyRange = Workbooks(Myname2).Sheets("training").Range(Cells(19, i), Cells(opend, i))
MyRange.Copy

That Doesn't work


****************************************
Set MyRange = Range(Cells(19, i), Cells(opend, i))
Workbooks(Myname2).Sheets("training").Range(MyRange).Copy
That Doesn't work as well


Any ideas, Howcan I copy a range from another workbook with the property cells?
Because with
Workbooks(Myname2).Sheets("training").Range(columni & "19", columni & opend).Copy I didn't have any problems

Thanks

 

[SkipVought]

I did not ask you for CODE.

I asked you for a REQUIREMENT.

Exactly WHAT are you trying to do??? NO CODE PLEASE!

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[Azban65]

I got two training matrix, One new generate by a macro I wrote with the new operator instruction and their number.
The second training matrix is the old one.
The code select the old file through a windows, and open it.
MyName is the new file and Myname2 the old file.

Now the macro copy the line with all the name of the operator from the old file, delete their training status in the new one (because they don't match the new column)
And then the macro check column by column in the old file if the name of the operator instruction match the name of one in the new file, if yes, it copies the training status of the operator from the old workbook in front of the operator name in the column corresponding in the newworkbook.

So I have 2 workbooks open, one "new" and one "old". And I need to copy data from the old to the new matching cell contents.

Thomas

 

[SkipVought]

What you describe, might be easier to accomplish using a query, rather than copy 'n' paste.

Is your data tabular, both source and destination?

Skip,
^{Don't let the Diatribe...
talk you to death!}
_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

Anyway, you may replace this:
Set MyRange = Workbooks(Myname2).Sheets("training").Range(Cells(19, i), Cells(opend, i))
MyRange.Copy
With this:
With Workbooks(Myname2).Sheets("training")
.Range(.Cells(19, i), .Cells(opend, i)).Copy
End With

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Azban65]

ermmm, My understanding of VBA is really limited. I do that by learning on forum etc...
I don't understand what you mean by Data tabular.
The both data are on the same row and nearly same column. the fact is that I look for the same OP instruction on the whole for each instructions.
It's working actually, but With the translation of the column number in letter, and I'm not really sure about this one...

Anyway it's working, but i really would like to know why I can't use the cell property on the copy and paste...

Thanks

 

[PHV]

why I can't use the cell property
Did you try my suggestion ?

 

[Azban65]

Thank you PHV,
Sorry I wrote the last answer as the same time as you answered me!
It work perfectly this way!

Many Thanks

subsiduary question:
How do I keep a button action link to the new workbook when i create and save it?

I did that
Windows(Workbookname).Activate
ActiveSheet.Shapes("Button 652").OnAction = "Sheet4.selectsheettraining"

But the button link to the parent file instead to the child file (if I can say it like that)


Any ideas?

Thanks

Thomas