Posts by Werner

Werner · Jul 12th 2006

Re: Vlookup a number and give the value of the cell next to it

It worked finally Wigi! Thank you for your help!

Regards,

Werner

Werner · Jul 12th 2006

Re: Vlookup a number and give the value of the cell next to it

Hi,

It does not work. In an Excel French version, it says #nom? or probably in english #name? . I had to translate the formula in order for Excel to understand it (Microsoft should have had let it in English!) :

=Recherchev(A2;B1:C50;2;false)

Or

=VLOOKUP(A2;B1:C50;2;false)

Werner

Werner · Jul 12th 2006

Hi,

I want to do a vertical lookup in an Excel sheet.

Let's say my formula is in cell A1.
I want to write a number in cell A2. The lookup in cell A1 will check in a list from B1 to B50. When it finds the same value in that range, I want the lookup to display the value from the cell just right of it, in the column C.

So for example, I write 1995 in A2. The lookup in A1 finds it at B10 and in A1 (where's the lookup formula), the value of cell C10 is displayed.

Thanks for any help!

Werner

Werner · May 26th 2006

Re: Converting decimal numbers to text with dot numbers

Thank you a lot Will Riley! It was exactly what I needed. It did the job! Now I can copy from Excel my decimal numbers in format number with two decimals in a Lotus sheet. Lotus considers them as real numbers now.

Regards,

Werner

Werner · May 26th 2006

Hi,

At work, we work with both Lotus 123 and Excel 2003. Lotus will be gone next year, but for now, the official mean to publish our reports is Lotus.

With my work, I copy/paste a Lotus page to Excel. I use the following macro to convert Lotus format numbers (which Excel considers as text) to real numbers (big thanks to Dave Hawley for this one 2 years ago) :

Code

Sub ForceToNumber()


    Dim wSheet As Worksheet
    For Each wSheet In Worksheets
        With wSheet
            .Range("IV65536") = vbNullString
            .Range("IV65536").Copy
            .UsedRange.PasteSpecial xlPasteValues, xlPasteSpecialOperationAdd
        End With
    Next wSheet
End Sub

Display More

Source : http://www.ozgrid.com/forum/sh…d.php?p=184087#post184087

The problem is that I need to send back this data in Lotus. Excel considers decimal numbers with a coma as real numbers and numbers with a dot as a text. This previous macro fixes that. However, Lotus works the other way. Only numbers with a dot are considered real numbers. So I would need to find a way to code a macro that converts any numbers in the Excel sheet to a number with a dot. It's a bit like doing the opposite operation.

For example, 6504 would become 6504.00 and 0,04 would change to 0.04 .

Is it possible? It would then be easy for me to simply copy and paste those cells in my Lotus sheet.

Thanks!

Werner

Werner · May 15th 2006

Re: Missing row when printing a splited sheet

I have found the solution in an old thread which is 3 years old! :
http://www.ozgrid.com/forum/sh…79&highlight=freeze+panes

In the Page Setup, there's a place for specifying a zone to be repeated in the printed pages. The zone which which must be repeated when printing was set to row 2 to 15. So row 1 was not included.

But I want to thank you all for your help!!!

Werner

Werner · May 15th 2006

Re: Missing row when printing a splited sheet

I am sorry. I have used the wrong words to explain my problem. I use MS Office 2003 in french version. And I mistranslated the option I used. I used Freeze panes and not split. In French, Split is "Fractionner" and freeze panes is "figer les volets". I just explained my problem with the wrong option.

But for the lack of rows, I tried using 15 rows instead of 16 and despite that, the first row is still missing. Even worst, I indeed tried the split option after that, and this one too does the same behavior.

I don't know what to say about that. I will continue to investigate this matter.

But I want to thank you for your help!

Werner

Werner · May 15th 2006

Hi,

I have a sheet which contain an horizontal split (Option Windows/Split). I have selected the row 16 to split the sheet so that I can have a header when I scroll down. However, when I print this data sheet, the first row, at the top of the screen, is missing. In the preview and on the printed sheets, the first row is missing on the next printed pages. In the first page, all is ok, but not on the next ones.

Does someone have an idea why Excel is behaving like that?

Thanks!!

Werner

Werner · Aug 17th 2005

Re: Controling macro activation conditions

It works great! Thx! And I solved the second condition. You are a professionnal Excel athlete norie hehe I've also added the complete code in this reply.

Regards,

Werner

Code

Sub Math_Sup_Ratio_Loyer()


    Dim CheckUp As Range
    Dim CheckUpVar As Variant
        
    Set ActiveRange = ActiveCell


    If ActiveSheet.Name = "PI" Then
         ' run your code
    Else
        Exit Sub
    End If


    Set CheckUp = ActiveRange
    CheckUpVar = CheckUp.Row
    
    If CheckUpVar < 16 Then
        Exit Sub
    End If


    If Worksheets("PI").Range("E" & CheckUpVar) <> "" And _
    Application.WorksheetFunction.IsNumber(Worksheets("PI"). _
    Range("E" & CheckUpVar).Value) Then
        'Rien, on continue.
    Else
        Exit Sub
    End If


    'We can continue with the code now.
End Sub

Display More

Werner · Aug 17th 2005

Hi,

I want to do a mathematic macro that I will activate in an active cell with a shortcut. But I don't want the macro to be able to launch itself if I am in the wrong sheet and below row 16. I have a problem doing those two conditions. Here's the beginning of my code. It gives me an error in the first If and I don't know if the other If is Ok for the row. Could anyone help me?
Thx!

Werner

Code

Sub Math_Sup_Ratio_Loyer()


    Application.ScreenUpdating = False
    
    Dim ActiveRange As Range
    Dim ws As Worksheet
    Dim CheckUp As Range
    Dim CheckUpVar As Variant
     
    Set ws = Sheets("Pi")
        
    Set ActiveRange = ActiveCell
    
    If ActiveSheet = ws Then
        'Nothing
    Else
        End
    End If
    
    Set CheckUp = ActiveRange
    CheckUpVar = CheckUp.Row


   If CheckUp < 16 Then
        End
   End If 


End Sub

Display More

Werner · Aug 16th 2005

Re: Managing workbooks creation

Thx norie!! It works perfectly! I included my code in this post after adding your modification. Again many thanks!

Regards,

Werner

Code

Sub CopiePI()


    Application.ScreenUpdating = False


    Sheets("PI").Select
    Sheets("PI").Copy After:=Sheets(6)
    Sheets("PI (2)").Select


    Dim Pi_Check As Long
    Pi_Check = 0


    Dim lstRw As Long
    
    'Déclare l'index de mon looping
    Dim I As Long
    Dim MyString As String
    Dim MyNumber As Double
    Dim wbNew As Workbook
    
    'Vérification s'il y a des PI.
        Pi_Check = Application.WorksheetFunction.CountA(Worksheets("PI (2)").Range("A8:A25"))
    
    If Pi_Check = 0 Then
        lstRw = 8
    Else
        'Je détermine le nombre de ligne d'unité locative que j'ai.
        Let lstRw = Sheets("PI (2)").Range("a65536").End(xlUp).Row
        
    End If
    
    For I = 8 To lstRw
    
        If Left(Worksheets("PI (2)").Range("U" & I).Value, 1) = "*" Then
    
            'On traite U pour extraire le nombre
            MyString = Worksheets("PI (2)").Range("U" & I).Value
            MyNumber = Mid(MyString, 2, (Len(MyString) - 3))
            Worksheets("PI (2)").Range("U" & I).Value = MyNumber
        End If
        
        If Left(Worksheets("PI (2)").Range("V" & I).Value, 1) = "*" Then
    
            'On traite U pour extraire le nombre
            MyString = Worksheets("PI (2)").Range("V" & I).Value
            MyNumber = Mid(MyString, 2, (Len(MyString) - 3))
            Worksheets("PI (2)").Range("V" & I).Value = MyNumber
        End If
    Next I
    
    Set wbNew = Workbooks.Add
    Windows("Loclis04.xls").Activate
    Sheets("PI (2)").Select
    Sheets("PI (2)").Copy Before:=wbNew.Sheets(1)
    Windows("Loclis04.xls").Activate
    Workbooks("Loclis04.xls").Sheets("PI (2)").Delete
    Workbooks("Loclis04.xls").Sheets("PI").Select
    Workbooks("Loclis04.xls").Sheets("PI").Range("A7").Select
    wbNew.Activate
    wbNew.Sheets("PI (2)").Activate
    wbNew.Sheets("PI (2)").Name = "PI"
    wbNew.Sheets("PI").Range("A2").FormulaR1C1 = "COPIE DE LA SECTION PARTIES D'IMMEUBLE"
    Application.ScreenUpdating = True
    wbNew.Sheets("PI").Range("A7").Select
    
End Sub

Display More

Werner · Aug 16th 2005

Re: Managing workbooks creation

Norie, you gave me the way for the solution! Creating the workbook that way will solve my problem. I have only a little problem adding my copy in thenew workbook. It says incompatible type. Could you look at the code and help me find why it does not work.
Thx!

Werner

Code

Sub CopieUE()


    Application.ScreenUpdating = False
    
    Dim wbNew As Workbook
    Set wbNew = Workbooks.Add
    Windows("Loclis04.xls").Activate
    Sheets("UE").Select
    'It's the following line that gives the error.
    Sheets("UE").Copy After:=Workbooks(wbNew).Sheets(1)
    Application.ScreenUpdating = True
    Sheets("UE").Select
    
End Sub

Display More

Werner · Aug 16th 2005

Re: Managing workbooks creation

Well the problem is that I created a macro that does a copy of a sheet named PI. First, it does a copy named PI (2) and changes some numbers to be properly prepared for a mathematic use in the real copy. Then a second copy is made to another workbook for the real work. But I must destroy the temporary copy into the original workbook, and then specify in the code the name of the new workbook to select the real copy in the new workbook.

Werner · Aug 16th 2005

Re: Managing workbooks creation

yes, they will be named the same.

Werner · Aug 16th 2005

Hi,

I need to create a macro that will create a new workbook and give a name to the workboox depending of the actual opened workbooks. For example, Workbook1, Workbook2, Workbook3 are opened. So my new workbook would be named Workbook4. The way it's named just before the number is not important. I just need to have a name with a number depending of the precedent workbooks. And my macro needs to copy in that newly created workbook the active sheet of workbook1. Does anyone have an idea about how I can do it?
Thx!

Werner

Werner · Aug 14th 2005

Re: Checking if there is an autofilter

Thx Jack! I appreciate a lot

Regards,

Werner

Werner · Aug 13th 2005

Re: Checking if there is an autofilter

I found the answer in a previous thread. Thx for the one year old answer Derk

http://www.ozgrid.com/forum/showthread.php?t=23116

Regards,

Werner

Werner · Aug 13th 2005

Hi,

I'd like to know how I could do a macro that does a validation by checking if there is or not an autofilter on a selection. It would be something like :

Code

If Selection.autofilter = true Then


'do something
End If

Is it possible?

Thx!

Werner

Werner · Aug 11th 2005

Re: Save number format in a variable

I've found again the solution by searching in the ozgrid forum database. Link :http://www.ozgrid.com/forum/showthread.php?t=35763

Here's the final code to my macro for converting monthly fees into annual ones :

Code

Application.ScreenUpdating = False
 
    Dim DblNombre As Double
    Dim StrNombre As String
    StrNombre = ""
    Dim ActiveRange As Range
    Dim ws As Worksheet
     
    Set ws = ActiveSheet
        
    Set ActiveRange = ActiveCell
    
    ActiveCell.Copy
    
    Worksheets("Formulaire").Select
    Worksheets("Formulaire").Range("AF1").Select
    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False


   
    'We put data in the inputbox.
    StrNombre = CDbl(Application.InputBox(Prompt:="Entrez le nombre mensuel à convertir en annuel:", Type:=1))
    
    StrNombre = StrNombre * 12
    ws.Activate
    ActiveRange.Activate
    ActiveCell = StrNombre
    ActiveCell.Select
    
    Worksheets("Formulaire").Select
    Worksheets("Formulaire").Range("AF1").Copy
    
    ws.Activate
    ActiveRange.Activate
    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
        
    Worksheets("Formulaire").Select
    Worksheets("Formulaire").Range("AF1").Select
    Selection.ClearContents
    Worksheets("Formulaire").Range("A15").Select
    
    ws.Activate
    Application.ScreenUpdating = True
    ActiveRange.Activate

Display More

Werner · Aug 11th 2005

Re: Save number format in a variable

I've a problem. My code works if I stay in the sheet "Formulaire". Does anyone know how to put in a variable what was the variable sheet tab (just like we put a cell place in a range variable) ? And then I must find a way to activate this sheet tab again just like we simply do sheet("Formulaire").select ?