Posts by CapG

I need to know, after a filter is applied to a column any results are retrieved for further processing except the headers.

I have put the filter criteria in a for loop and inside the loop after a filter criteria is applied, I need to know if there are any results to calculate which will be in if condition, if not then then jump to next criteria

When the below macro is ran, the memory unavailable error occurs after the "'Fails Rate" comment at the below line causing it to terminate the execution abruptly.

Below code is the replica of the manual steps that we must do in order to preserve the conditional formatting formulas for arrow of Trend Analysis.

Please suggest a work around to this issue

sh.Cells(2, col).EntireColumn.Insert

I have a table of data based on which we have inserted a simple bar chart but the colors needs to be changed dynamically based on the criteria2 given beside just like conditional formatting.

Is this possible

Thanks you. that worked!

I have attached a sample excel file where when a user enters a text say from B3 to B5 Expert, Intermediate or beginner, it should lookup colum M and enter a number in the 'No' column using worksheet change function.

I am trying to create a chart (any type) that summarizes the table attached below. So for each topic there are X,Y,Z resources depicting their proficiency levels Expert, intermediate and so on. So If we input a level say Expert to a resource X must hold 50 points for that resource and sum up and if it falls between 85-99 then it must be colored green in the chart and the criteria is given in the attached spreadsheet.

Can we have have all the topics in a chart where we can filter per resource and proficiency levels

I would want to highlight cells on each Friday of the week for those teams in the same order as shown in the spreadsheet below. The cells colored red with text "Fun Time" should be applied as and when the conditional formatting is set to a formula.

Is it possible through conditional formatting alone?

I have a .txt delimited data which gets imported to excel and I do text to columns in which date column (Value Date) is not changing its format to do further calculations

While turning text to columns I change the type to date DMY and there is no change seen after splitting into columns

Is there any alternative approach to this?

Thanks, That worked!

I am getting the error message at the line in the below code snippet.

Any idea on resolving this issue?

 Conn1.Open sconnect

 Dim Conn1 As ADODB.Connection
   Dim rs As ADODB.Recordset
   Dim mCmd As ADODB.Command
   
   DBPath = ws.Parent.FullName
   sconnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & DBPath & ";Extended Properties=""Excel 12.0 XML;HDR=Yes;IMEX=1"";"

    Set Conn1 = New ADODB.Connection
   Conn1.Open sconnect

Hi,

At the end of macro execution, I am getting "the picture is too large and will be truncated" and the entire result is getting erased.

Any suggestions as to how to overcome with this issue?

It worked.Thank you for the help.

How could I add that condition?

Hi,

I have a code which loops through cells after applying autofilter which includes the unfiltered data.

After entering while loop, the line

If dico_Triparty.Exists(CStr(wk.Sheets(1).Cells(i, nb_cl))) = True Then

is searching all unfiltered data and retrieving values to Triparty_pos file

Set wk = Workbooks.Open(Pilot.Range("Mamout"))

i = 2
side_cl = Cells.Find("Side").Column
nb_cl = Cells.Find("Code").Column
Comment_cl = Cells.Find("Comment").Column
ActiveSheet.Range("A:AT").AutoFilter Field:=side_cl, Criteria1:="B"

While Not wk.Sheets(1).Cells(i, 1) = Empty
    
    If dico_Triparty.Exists(CStr(wk.Sheets(1).Cells(i, nb_cl))) = True Then 
        
        ActiveSheet.Range("A:AT").AutoFilter Field:=nb_cl, Criteria1:=CStr(wk.Sheets(1).Cells(i, nb_cl))
        
            If (CStr(wk.Sheets(1).Cells(i, nb_cl).Offset(0, -3))) = "Cash" Then
                If Application.WorksheetFunction.Subtotal(3, Columns(nb_cl)) - 1 > 1 Then
                       Set rng = Range("G2:G" & Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
                       x = 0
                        For Each cll In rng
                            ReDim Preserve a(x)
                            a(x) = cll.Value
                            x = x + 1
                        Next
                        z = Join(a, ",")
                    Triparty_Pos.Cells.Find(CStr(wk.Sheets(1).Cells(i, nb_cl))).Offset(0, 5) = z
                Else
                    Triparty_Pos.Cells.Find(CStr(wk.Sheets(1).Cells(i, nb_cl))).Offset(0, 5) = wk.Sheets(1).Cells(i, nb_cl).Offset(0, -1)
                End If
            
                
            ElseIf (CStr(wk.Sheets(1).Cells(i, nb_cl).Offset(0, -3))) = "Repo" Then
                If Application.WorksheetFunction.Subtotal(3, Columns(nb_cl)) - 1 > 1 Then
                       Set rng = Range("D2:D" & Cells(Rows.Count, 1).End(xlUp).Row).SpecialCells(xlCellTypeVisible)
                       x = 0
                        For Each cll In rng
                            ReDim Preserve a(x)
                            a(x) = cll.Value
                            x = x + 1
                        Next
                        z = Join(a, ",")
                    Triparty_Pos.Cells.Find(CStr(wk.Sheets(1).Cells(i, nb_cl))).Offset(0, 5) = z
                Else
                    Triparty_Pos.Cells.Find(CStr(wk.Sheets(1).Cells(i, nb_cl))).Offset(0, 5) = wk.Sheets(1).Cells(i, nb_cl).Offset(0, -4)
                End If
    
            Triparty_Pos.Cells.Find(CStr(wk.Sheets(1).Cells(i, nb_cl))).Offset(0, 6) = wk.Sheets(1).Cells(i, Comment_cl)
            ActiveSheet.Range("A:AT").AutoFilter
            End If
            End If
i = i + 1
Wend

Yes, when I opened in notepad the values are correctly shown. I'm opening it in excel since that excel.csv should be fed to another application and I cannot upload .txt which is the notepad version of the same.

Is it possible to prevent the conversion when opening in excel?

Not onscreen. Only after when the file is saved and closed as .csv. When I re-open the same file the formats are gone.

I have macro that creates a new workbook process the data and converts column N into text and saves in .csv format.

Now, if Column N has value=052880806 saved as text. After saving as .csv that value truncates leading 0(s) and shows in a scientific manner 5.29E+14.

While debugging I see the text format preserving until the file save line, after wb.Saveas book1.csv,XLCSV line the formats are gone.

I cannot have the excel saved in other formats except .csv because the file is getting loaded to another application which allows only .csv type of files. Is there a workaround?

Hi,

Thank you rory and RoyUK for reverting but I figured later that the formula should be locked like this $C1:$C50.

=$O2*Index('[List macro.xlsm]GFR'!C:C, Match($F2,'[List macro.xlsm]GFR'!A:A,0))

in VBA is not working.

Range("P2").formula="=O2*Index('[List macro.xlsm]GFR'!C:C,Match(F2,'[List macro.xlsm]GFR'!A:A,0))"

Great Info!

Thank you so much