Posts by alxx_ander

Quote from royUK

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.

Quote from royUK

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.

Quote from dangelor

Possibly using a for loop...

Code

    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"
            Else
                .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 "objshell.open" 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

Hi,
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
UserForm1.Show
Bitacora2 = Bitacora
Call OpenAnyFile(Bitacora2)
Application.Wait (Now + TimeValue("0:00:10"))
SendKeys ("^a")
SendKeys ("^c")

This if the code in the user form

Private Sub CommandButton1_Click()
Bitacora = Application.GetOpenFilename(Title:="Elegir Archivo", filefilter:="PDF files (*.pdf), *.pdf")
FileNum = FreeFile()
If Bitacora = "" Then
Exit Sub
End If
CommandButton2.Visible = True
End Sub
Private Sub CommandButton2_Click()
Unload Me
End Sub

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.

Re: Can't detect blank spaces between words

Hi Luke, it worked perfectly .....

Thank you very much !!!

Hi,
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]

[/TABLE]

Re: Using the > Symbol on vba Worksheet Funtion

Thank KjBox, I finally got it Working like this :

Quote

Range("I2:I" & LastRow).Formula = "=COUNTIFS(Fecha_de_Caducidad,"">""& " & c & ",Sku," & a & ")"

How can I close the thread or mark as solved ???

Re: Using the > Symbol on vba Worksheet Funtion

No Mike, the formula string that I need is :

Quote

=CONTAR.SI.CONJUNTO(Fecha_de_Caducidad,">"&C2,Sku,A2) ( In Spanish ) or in English =COUNTIFS(Fecha_de_Caducidad,">" & C2,Sku,A2)

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.

Re: Using the > Symbol on vba Worksheet Funtion

Hi Mike,
If I use it as you say I get an error Message 1004, Error defined by the application or the object.

Hello !!!

I'm trying to insert a formula to a cell but it doesn't work, if I use it directly on the excel sheet it runs fine.

Range("I3:I" & LastRow).Formula = "=COUNTIFS(Fecha_de_Caducidad," > " & " & c & ",Sku," & a & ")"

Any help its appretiated.

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

Public Sub Cmbdia_Change()


End Sub


Private Sub CmdAceptar2_Click()
Me.Hide
End Sub


Private Sub CmdNo_Click()
Unload Me
End Sub


Private Sub CmdSi_Click()
Label2.Visible = True
Cmbdia.Visible = True
CmdAceptar2.Visible = True
End Sub


Private Sub Label2_Click()


End Sub


Private Sub UserForm_Initialize()


Label2.Visible = False
Cmbdia.Visible = False
CmdAceptar2.Visible = False


Cmbdia.AddItem "Sábado"
Cmbdia.AddItem "Domingo"


End Sub

Thanks in advance !!!

Re: Open a File Using Vba

Hi cytop the problem is that the second file doesn't open, I get an error message, saying that the file is not found

But I think the path is ok.

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 ???

Private Sub CmdAceptar_Click()


FrmCapturaFecha.Hide


Dim Danexcell As Range
Dim Fecha As String
Dim ruta As String, direccion1 As String


Fecha = (TextBox1)


LastRow = Range("A" & Rows.Count).End(xlUp).Row


Set Danexcell = Range("A6:I" & LastRow)


Danexcell.Copy


Application.Workbooks.Open ("I:\Operaciones Irapuato\Danexpress\Ajustes.xlsx")


Range("A3").Select


Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False


ruta = "I:\Operaciones Irapuato\Danexpress\Programas\"
fichero1 = "DISP DANEXPRESS " & Fecha & ".xlsx"
direccion1 = ruta & fichero1
Workbooks.Open (direccion1)

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 :

Sub Horarios_proxy()
    Dim Rango_Rutas As Range
    Set Rango_Rutas = Range("B8:C29")
    Rango_Rutas.Select
    Selection.Copy
 
    Range("B1048575").End(xlUp).Offset(1, 0).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
 
    LastRow1 = Range("A" & Rows.Count).End(xlUp).Row + 1
 
    LastRow2 = Range("B" & Rows.Count).End(xlUp).Row
 
    Dim d As String
 
    d = ("D" & LastRow1)
    e = ("E" & LastRow1)
    f = ("F" & LastRow1)
    g = ("G" & LastRow1)
    h = ("H" & LastRow1)
    i = ("I" & LastRow1)
    l = ("L" & LastRow1)
    m = ("M" & LastRow1)
 
    Range("D" & LastRow1 & ":D" & LastRow2) = "6:30"
    Range("F" & LastRow1 & ":F" & LastRow2) = "=IF((" & e & "<=" & d & "),""On Time"",(" & e & "-" & d & "))"
    Range("G" & LastRow1 & ":G" & LastRow2) = "7:15"
    Range("I" & LastRow1 & ":I" & LastRow2) = "=IF((" & h & "<=" & g & "),""On Time"",(" & h & "-" & g & "))"
    Range("J" & LastRow1 & ":J" & LastRow2) = "=IF(N(" & h & ")<N($Y$8),""Antes de 6:45"",IF(AND(N(" & h & ")>=N($Y$8),N(" & h & ")<=N($Z$8)),""06:45 - 07:15"",IF(AND(N(" & h & ")>N($Z$8),N(" & h & ")<=N($Z$9)),""07:16 - 07:45"",IF(AND(N(" & h & ")>N($Z$9),N(" & h & ")<=N($Z$10)),""07:46 - 08:15"",""Despues de 8:15""))))"
    Range("K" & LastRow1 & ":K" & LastRow2) = "1"
    Range("M" & LastRow1 & ":M" & LastRow2) = "=IF((" & l & "<=0.791666666666667),""On Time"",(" & l & "-0.791666666666667))"
 
    Range("N" & LastRow1 & ":N" & LastRow2) = "=(" & l & "-" & h & ")"
    Range("O" & LastRow1 & ":O" & LastRow2) = "=IF(N(" & l & ")<N($AA$8),""Antes de 15:00"",IF(AND(N(" & l & ")>=N($AA$8),N(" & l & ")<=N($AB$8)),""15:00 - 17:00"",IF(AND(N(" & l & ")>N($AB$8),N(" & l & ")<=N($AB$9)),""17:01 - 19:00"",IF(AND(N(L381)>N($AB$9),N(" & l & ")<=N($AB$10)),""19:01 - 22:00"",""Despues de 22:00""))))"
    Range("P" & LastRow1 & ":P" & LastRow2) = "=IF(" & f & "=""On Time"",1,"""")"
    Range("Q" & LastRow1 & ":Q" & LastRow2) = "=IF(" & i & "=""On Time"",1,"""")"
    Range("R" & LastRow1 & ":R" & LastRow2) = "=IF(" & l & "<=N($AB$9),1,"""")"
    [B][COLOR=red]Range("S" & LastRow1 & ":S" & LastRow2) = "=IF(" & f & "=""On Time"",1/COUNTIF($X$8:$X$38," <= "&$W$8),"""")"[/COLOR][/B]
    Range("T" & LastRow1 & ":T" & LastRow2) = "=IF(" & i & "=""On Time"",1/COUNTIF($X$8:$X$38," <= "&$W$8),"""")"
    Range("U" & LastRow1 & ":U" & LastRow2) = "=IF(" & m & "=""On Time"",1" / "COUNTIF($X$8:$X$38," <= "&$W$8),"""")"
    Range("$W$8") = "=MAX(A:A)"
 
End Sub

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!!!