Posts by alxx_ander

    What works? Filling down as I described should be quite fast, converting to a Table will allow the formulas to self populate as rows are added.

    Switching calculation to manual might speed things up.

    The Filling down worked, I converted the range to a table and then applied manually the filling down it took around 6 minutes, then I applied the filling down by vba and it also took 6 minutes, I just have tryied also to change the calculation to manual and at the end of the code a switched again to automatic and it took 15 minutes,

    The range or table has 50,000 rows I usually don´t add rows to this table but I have to recalculate the formula since other values in the worbook change, so what I do after I recalculate the formula I turn to values the formula range to avoid slowing down the worbook.

    Enter the formula manually and hover the cursor over the bottom right of the cell. If the data is in a table format the formula will autofill down.

    Better still convert the data to an Excel Table and formulas will auto-populate.

    Thank royUK it works but it takes the same time ( 6 minutes ) and I´m lokking for a way to do it faster.

    Possibly using a for loop...

        With Scorecard.Worksheets("Visit List")
            lastrow4 = .Cells(Rows.Count, 34).End(xlUp).Row
            For i = 2 To lastrow4
                If IsError(WorksheetFunction.VLookup(.Range("AG" & i), Worksheets("Readings").Columns("AF:AG"), 2, 0)) Then
                    .Range("AH" & i).value = "Not tagged"
                    .Range("AH" & i).value = "Tagged"
                End If
            Next i
        End With

    Hi dangelor thanks for the reply but the looping method Works but it took four times ( 25 minutes ) than my previous method ( Entering the formula to the cell takes 6 minutes to run)

    Hi, I have a range of around 50,000 rows where I Enter by VBA a formula but it takes a lot of time to run, so I'm trying to use Evaluate function as an array formula to see if it runs faster but without success.

    Scorecard is the name of the workbook.

    Scorecard.Worksheets("Visit List").Range("AH2:AH" & lastrow4).Value = Sheet8.[Index(IF(ISERROR(VLOOKUP(AG2,'Readings'!AF:AG,2,0)),""Not tagged"",""Tagged""),)]

    Any help wpould be appreciated.

    Regards Alex

    Hi Kenneth thaks for your reply, the pdf files that I´m trying to select and copy are all text and have the same format, if I open manually one of these PDF with Adobe Reader I can select all and copy using CTR + A and CTRL + C, but I want to automatize these process for the final user, by the way I cant use a third party program.

    This is the function I use to open the PDF file, but after the file is opened I cant select and copy, the sendkeys ("^a") select the text (code) inside vba instead of selecting the text in the PDF file, watching your code on the other thread I see or at least I think that I need the path of the adobe reader, but the thing is that this excel file is going to be used by several final users on several computers and I dont know the path of their adobe readers, it is posible to track or identify the path after the PDF file is opened, after the "" line ? so I can use it on the code of the other thread.

    Function OpenAnyFile(strPath As String)
      Set objshell = CreateObject("Shell.Application")
      objshell.Open (strPath)
      End Function

    Regards Alex

    Im trying to copy text from a PDF File to an excel workbook, I have managed to open the PDF File with a userform the user selects the file to open using Application.GetOpenFilename then I use a Function I found to open the PDF file, so far so good, but then when I try to use sendkeys to select all and copy it doesnt work, this file ( Macro ) is going to be used on several computers on several places so I dont know the path of the program used to open the PDF File, any ideas on how to select and copy the text from the PDF File ? Thanks in advance !

    Public Bitacora
    Dim Bitacora2 As String
    Sub Bitacora_a_Excel()
    Application.ScreenUpdating = False
    Bitacora2 = Bitacora
    Call OpenAnyFile(Bitacora2)
    Application.Wait (Now + TimeValue("0:00:10"))
    SendKeys ("^a")
    SendKeys ("^c")

    This if the code in the user form

    Hi, I have a sheet with all the cells with black background on this sheet I have a pivot table, when I refresh the pivot table ( using VBA ) the cells format of two rows below the pivot table change to white background, Is there someway to avoid this, by now I´m using vba to apply the black background to the two rows below the pivot table again after refreshing but I refresh this pivot table a lot of times so I dont like this option because it slows down the refreshing pivot table code, thanks in advance for the help.

    I'm trying to delete blank spaces I have between words on the same cell, but I can't do it, I have used the funtion " Trim ", and I have tried Text to Column but it doesn't work, looks like excel doesn't detect the blank spaces.

    A Cell content looks like these

    25216 DANY UVA 125G 4 PCE 3.20 12.80

    I'm attatching a file, I hope you can find whats going on.

    Thanks in advance !!!
    [TABLE="width: 80"]


    [TD="class: xl63, width: 80"][/TD]



    Re: Using the > Symbol on vba Worksheet Funtion

    Yes Fecha de Caducidad and Sku are named ranges of the same size both are refering to a whole column in another sheet, Fecha de Caducidad =Reporte!$Y:$Y and Sku =Reporte!$B:$B, a is a 5 digit number to identify a product, and c is a date the shelf life of the product, what I'm trying to get is the count of the products that has the same Sku and a date bigger than c, the probles is when I use the > symbol.

    Hello Everybody!!!

    I want to use a value from a combox of a userform in another userform as a Variable, any advices ???

    This is the code of the first Userform and the data I want to use is from the Cmbdia combox

    Thanks in advance !!!

    Hello Everybody :

    I'm trying to open a file using vba and part of the name I'm getting it from a textbox but it's not working, the problem in on the second file I'm oppening, any advise ???

    Thanks in advance !!!

    Hi Everybody !!

    I have a problem with a Macro:
    I`m trying to introduce a formula COUNTIF thru the macro but I only get the word "False", not even the formula with an error.
    I have used the forumla directly in exel and it run fine.

    This is the formula in excel ( spanish ) :

    =SI(F367="On Time";1/CONTAR.SI($X$8:$X$38;"<="&W8);"")

    And this is the macro :

    The rest of the Macro runs fine it-s just the formula on red.

    Any Help or sugestion will be apreciatted.

    Hi Everybody !!!
    I need some help I'm triyng to get a Macro that deletes rows if a specific condition is met I'm using an Autofilter with the condition, but when I try to delete the Visible Rows I Get an Error Message " '1004' in execution time, Error defined by the application or the object.

    This is the code I'm Using :

    [/SIZE][/FONT]Sub Procesar_Transfer()Application.Workbooks.Open ("D:\Mis Documentos\Alex\2011\Resumen Operativo Pachuca\Transfer\Reporte Transfer2.xls")ActiveSheet.Rows("1:3").EntireRow.DeleteActiveSheet.Columns("a:b").EntireColumn.Delete Dim STable As Range LastRow1 = ActiveSheet.Range("A" & Rows.Count).End(xlUp).RowSet STable = ActiveSheet.Range("A1:O" & LastRow1)STable.SelectWorkbooks("Copia de Transfer & PPDE'S.xlsm").ActivateSheets("Transfer").Activate ActiveSheet.AutoFilterMode = FalseActiveSheet.Range("A65536").End(xlUp).Offset(1, 0).Select Workbooks("Reporte Transfer2.xls").ActivateSelection.CopyWorkbooks("Copia de Transfer & PPDE'S.xlsm").ActivateSheets("Transfer").ActivateSelection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=FalseLastRow2 = Range("A" & Rows.Count).End(xlUp).RowSet rngFilter = Range("A1:S" & LastRow2)rngFilter.AutoFilter Field:=7, Criteria1:="<0" rngFilter.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete

    Any Help will be really apreciated!!!