Posts by luzmen

Thanks Carim for your reply,

however i had notice that it will only segregate the Top with the same name. Name-4 should included in Top 4 not in Top 5 as per result of your formula, and Name-3, Nam-6, & Name-10 should be Top 5 instead of Top 6, Top 7, & Top 8 respectively.

Sub ClearIfTrue()


For Each cell in Range("RangeStart:RangeEnd")
     If cell.value >  TRUE  Then
              Range("RangeStart:RangeEnd").ClearContents
     End If


Next cell 
    End Sub

Note: I used selected range instead of entire range of the cell because it will take long time to finish the command.

Hope it helps (i did not test it).

Hi & Hello:

I have a list of data that i wanted to sort for Top amount. I can create it with a formula base on the number of items to be listed in the Top, however, the formula is only for individual items. I want a report that list even duplicate items and aggregate it to calculate for Top rows. I attach sample file for easy reference.

P.S. If it is possible with formula, its better...

Thank you in advance.

Hi everyone,

Is there any formulas to add spaces in a cell text value?

Example: ABCDE result to: 1.) A B C D E (1 space in between) or 2. A B C D E (customize space in between).

Or in short i can judge how many spaces between text value. I've googled it and found only numeric value can customize spaces in between through Format Cells > Number > Custom, can't find it with text value.

I already thought of using =MID(B1,1,1)&" "&MID(B1,2,1)&" "&MID(B1,3,1)&" "&MID(B1,4,1)&" "&MID(B1,5,1) , but the cell value will not permanently have the same numbers of characters.

Is this possible with formula?

Thanks in advance.

Hi and Hello,

I have some compile error: Variable not defined at this code:

Sub CreatePDFForms()
Dim PDFTemplateFile, NewPDFName, SavePDFFolder, LastName As String
Dim ApptDate As Date
Dim CustRow, LastRow As Long
With Sheet1
LastRow = .Range("A14").End(xlUp).Row 'Last Row
PDFTemplateFile = .Range("B16").Value 'Template File Name
SavePDFFolder = .Range("B18").Value 'Save PDF Folder
OpenURL "" & PDFTemplateFile & "", Show_Maximized
Application.Wait Now + 0.00006


For CustRow = 5 To 5 'LastRow
LastName = .Range("A" & CustRow).Value
ApptDate = .Range("G" & CustRow).Value
End With
End Sub

The error was at Show_Maximized... how to resolved this problem?

Thanks in advance...

thank you, works with INDIRECT. But when i closed the source workbook, it #REF!. So this mean i should open the source workbook?

Hi and Hello,

I have this formula in cell H2:
=INDEX(F2,MATCH(A2,G2,0))

where:
cell F2 = 'C:\Users\mpc\Desktop\[DataFile1.xls]1'!B2:B5000
G2 = 'C:\Users\mpc\Desktop\[DataFile1.xls]1'!A2:A5000

But the result is #N/A.. I already double check the DataFile1.xls, ID codes for A2 and sheets name, all were correct..

What is the correct formula for that?

Thank you.

I searched a code as follows:

On Error Resume Next




    Dim Imp_Row As Integer
    Dim Imp_Col As Integer
    Dim Table1 As Range
    Dim Table2 As Range
    Dim cl As Range


    Set Table1 = Range("A2", Range("A2").End(xlDown))


[COLOR=#0000FF]Set Table2 = Workbooks("File-A-Y1.xlsx").Sheets("1").Columns("A:B")[/COLOR]


    Imp_Row = 2


    Imp_Col = 7


    For Each cl In Table1
[COLOR=#0000FF]Cells(Imp_Row, Imp_Col) = Application.WorksheetFunction.VLookup(cl, Table2, 2, False)[/COLOR]
        Imp_Row = Imp_Row + 1
    Next cl


    MsgBox "Done"

The problem is, i will specify every arrays/reference in vlookup.. I want to be like this with the above highlighted lines:
Set Table2 = Workbooks(" =range B ").Sheets(" =range C ").Columns(" =range D : range E ")

Cells(Imp_Row, Imp_Col) = Application.WorksheetFunction.VLookup(cl, Table2, =range F, False)

I tried with the following code as stated below but no data has been transferred only appear the Msgbox "Done":

Set Table2 = Workbooks([COLOR=#FF0000]Range("B").Value[/COLOR]).Sheets([COLOR=#FF0000]Range("C").Value[/COLOR]).Columns([COLOR=#FF0000]Range("D").Value & ":" & Range("E").Valu[/COLOR]e)


Cells(Imp_Row, Imp_Col) = Application.WorksheetFunction.VLookup(cl, Table2, [COLOR=#FF0000]Range("F").Value[/COLOR], False)

Hello,

I have a main report worksheets that gets data from multiple workbooks thru VLOOKUP function. I don't have any problem with a formula, but in VBA code, i can't figure what to do.

Attach are my sample files (in Zip Files coz it has 3 files). My sample files only have a little data but my real files have at least 1,000 rows per worksheets.

Thanks in advance

I got it right now... i just combine INDIRECT functions..

Hello,

I have some difficulty in solving a formula for COLUMNS. I want to get the equivalent number for the last column in a given range.

This is my table looks like:
[TABLE="border: 1, cellpadding: 1, width: 500"]

A

B

C

Column where to Start

Column where to End

Formula to get the number of End columns

G

I

[/TABLE]

I know the formula is =COLUMNS(G:I) and the result is 3. How do i write the COLUMNS formula that instead of typing G:I, it will locate the stated value in column A and column B.

I tried this formula: =COLUMNS(A3&":"&B3) but the result is #VALUE!.

Anybody knows if this is possible with formula?

Thanks in advance.

Re: Looping through Columns when Copying cell value to Another Workbooks

I had made it works!

For references to other searchers, heres the code:

Sub Button1_Click()


Dim bk As Workbook
Dim bSave As Boolean
Dim lRow As Long
Dim xPath As String


Application.ScreenUpdating = False
xPath = Application.ThisWorkbook.Path


On Error Resume Next
  Set bk = Workbooks("reportworkbook")
On Error GoTo 0
If bk Is Nothing Then
  bSave = True
  Set bk = Workbooks.Open(xPath & "\reportworkbook.xlsx")
End If


    With ThisWorkbook.Sheets("DATA")
      
        lRow = .Cells(Rows.Count, "D").End(xlUp).Row
        For i = 2 To lRow
            bk.Worksheets("Sheet1").Range(.Range("D" & i).Value).Value = .Range("B" & i).Value
        Next i
            
    End With
        
If bSave Then bk.Close Savechanges:=True
Application.ScreenUpdating = True


End Sub

Re: Looping through Columns when Copying cell value to Another Workbooks

The template has been protected, it can't be modified the format and inserting rows.. only the cells that you input the value were unprotected.

The transfer-to-workbook is only my own conversion list...

Quote from Carim;798361

Indeed the re-structuring would have been ideal ...

A couple of questions :

1. Is the template structure fully frozen ...?

2. Have you established you own conversion list ...?

Display More

Re: Looping through Columns when Copying cell value to Another Workbooks

Quote from Carim;798351

Hello,

You are right ... when dealing with over 400 ranges ... a Loop is a much better solution ...

However, in order to build a loop ... you do need a consistent worksheet structure ...

From the code you have posted, it looks like the 'reportworkbook' is not structured at all ... which makes the challenge extremely harder ...

Display More

Carim,

I have notice that the template has a column with a code.

Please see again my attached files.

Thanks.

Re: Looping through Columns when Copying cell value to Another Workbooks

Thanks Carim for your reply..

Sad to say, I can't re-structure the reportworkbook. The template is given by the requiring agency. All i can do is to put a value on it.

Hi everyone,

I have this code to copy cell value to another workbook at specified range, it works properly.

Dim bk As Workbook
Dim bSave As Boolean
Dim lRow As Long


On Error Resume Next
  Set bk = Workbooks("reportworkbook")
On Error GoTo 0
If bk Is Nothing Then
  bSave = True
  Set bk = Workbooks.Open("C:\Users\mpc\Desktop\EXCEL VBA\reportworkbook.xlsx")
End If


    With ThisWorkbook.Sheets("DATA")
      
        bk.Worksheets("Sheet1").Range("B2").Value = .Range("B2").Value
        bk.Worksheets("Sheet1").Range("E2").Value = .Range("B3").Value
        bk.Worksheets("Sheet1").Range("H4").Value = .Range("B4").Value
        bk.Worksheets("Sheet1").Range("F15").Value = .Range("B5").Value
        bk.Worksheets("Sheet1").Range("G20").Value = .Range("B6").Value
        bk.Worksheets("Sheet1").Range("H30").Value = .Range("B7").Value
        '---and so on for at least 400 cells range----


    End With
    
If bSave Then bk.Close Savechanges:=True

Is there any possibilities that instead of coding bk.Worksheets("Sheet1").Range("B2").Value = .Range("B2").Value every cell range, it will only loop through a columns?

Example: bk.Worksheets("Sheet1").Range(" =cell C2 of the active worksheet ").Value = .Range(" =cell B2 of the active worksheet ").Value

I ask this situation because i have at least 400 cells ranges to fill and to lessen the codes. And also for proper monitoring of the exact cell range to fill.

I attached the files for proper preferences.

Thank you in advance.

Re: Delete Entire Rows from raw source Sheet that selected from ListBox and Auto Refr

Thanks Max, its explain well..

Now, i encountered a problem... it well also delete row with other ID#..

Re: Delete Entire Rows from raw source Sheet that selected from ListBox and Auto Refr

Can someone explain me why it will delete the row before the selected item/row with this code?

indexi = ListBox1.ListIndex + 1

Re: Delete Entire Rows from raw source Sheet that selected from ListBox and Auto Refr

Thanks Max161 for your reply... i tried what you say, still compile error appears and delete the row before the exact items.

However, i had manage to correct the code and now its working properly.

Private Sub DelFR1st_Click()    
    Dim indexi As Long
    Dim answer As Integer
    
    answer = MsgBox("Are you sure you want to remove/delete this item?", vbYesNo, "")
    
    If answer = vbYes Then
        indexi = ListBox1.ListIndex + 2
        If indexi <> -1 Then
            Worksheets("FR1st").Rows(indexi).EntireRow.Delete


        End If
        LoadData
    Else
    End If
End Sub

Hi everyone again,

I have this code to display in the listbox and update it if necessary. Now, I added a command button with a code to delete the selected item in the ListBox, but there is a "Compile Error: Method or data member not found".

Please note that the source of the listbox with a range name "FilterFR1st" is from other sheets which filtered from the raw source sheet. This is due to filtering for specific ID. Raw source worksheets name is "FR1st".

Code where Compile Error appears

ListBox1.ListFillRange = "a:a"

Code for command button "Delete"

Private Sub DelFR1st_Click()
    Dim indexi As Long
    Dim answer As Integer
    
    answer = MsgBox("Are you sure you want to remove/delete this item?", vbYesNo, "")
    
    If answer = vbYes Then
        indexi = ListBox1.ListIndex + 1
        If indexi <> -1 Then
            Worksheets("FR1st").Rows(indexi).EntireRow.Delete
            ListBox1.ListFillRange = "a:a"
        End If
    Else
    End If
End Sub

Whole code of my UserForm:

Option Explicit
Dim rData
Dim oCtl As MSForms.Control
Dim iX As Integer


Private Sub SaveFR1st_Click()
   
    With Me
        For Each oCtl In .Controls
            If TypeName(oCtl) = "TextBox" And IsNumeric(oCtl.Tag) Then
                iX = oCtl.Tag
                Sheets("FR1st").Cells(Me.ListBox1.ListIndex + 2, iX) = oCtl.Value
                Sheets("FR1st").Cells(Me.ListBox1.ListIndex + 2, 6) = TextBox34.Value
            End If
        Next oCtl
    End With


    LoadData
End Sub




Private Sub ListBox1_Click()


    With Me
        For Each oCtl In .Controls
            If TypeName(oCtl) = "TextBox" And IsNumeric(oCtl.Tag) Then
                iX = oCtl.Tag
                oCtl.Value = .ListBox1.List(.ListBox1.ListIndex, iX - 1)
            End If
        Next oCtl
    End With
End Sub




Private Sub UserForm_Initialize()
    LoadData
End Sub




Sub LoadData()


    Set rData = [FilterFR1st]
    TextBox34.Value = Format(Date, "mm/dd/yyyy")
    With Me.ListBox1


        .ColumnCount = 6
        .ColumnWidths = "0;83;100;95;90;0"
        .List = rData.Value
    End With
End Sub




Private Sub CloseFR1st_Click()
    Unload Me
End Sub




Private Sub DelFR1st_Click()
    Dim indexi As Long
    Dim answer As Integer
    
    answer = MsgBox("Are you sure you want to remove/delete this item?", vbYesNo, "")
    
    If answer = vbYes Then
        indexi = ListBox1.ListIndex + 1
        If indexi <> -1 Then
            Worksheets("FR1st").Rows(indexi).EntireRow.Delete
            ListBox1.ListFillRange = "a:a"
        End If
    Else
    End If
End Sub

Hope i explain it clearly...

What is the code to delete the entire row from raw source sheet with the selected item in the ListBox? I want also an Auto Refresh from the ListBox after deleting the record..

Thank you in advance.