Posts by abccells

abccells · Apr 4th 2020

OMG to fast replies and helping intentions!

Thanks to all the top experts....

Thank you you all

jolivanes, Justin Doward & Royuk

Thx for taking interest

Mumps, AlanSidman

abccells · Apr 2nd 2020

Friends

I want to transpose the variable columns to single column in the next page like

Original

A1,A2,A3,A4

B1,B2,B3

C1,C2,C3,C4,C5

...

To next page 'Final' like

A1

A2

A3

A4

B1

B2

B3

C1

C2

C3

C4

C5

...

One by one

Normal transpose doesn't work for me.

Thx in advance

abccells · Feb 25th 2014

Re: unique value 3 columns combined & sum

I did changes the following in the code
Changed ur code as folows to paste from B column

Code

With Sheets("PRN")
        .Range("B1:G1").Value = Array("Bank Name", "Cheq/DD No.", "Cheq/DD Date", "Amount", "to check unique value    :-)")
        .Range("B2").Resize(c, 5) = ray
        .Range("B2").Resize(c, 5).Sort .Range("B2"), xlAscending
    End With

And added this to delete from "F" Column and insert "A" Column with Serial Numbers

Code

With Range("A2:A" & Range("D" & Rows.Count).End(xlUp).Row)
    .Cells(1, 1).Value = 1
    .DataSeries Rowcol:=xlColumns, Type:=xlLinear, Step:=1, Trend:=False
    End With
    Columns("F:H").Select
    Selection.ClearContents
    Range("a2").Select
    ActiveWindow.FreezePanes = True

But how to add total in last Row as follows
Total in End of "B" Column and Sum in "E" Column
Mike,
Did I do right changes?
What Shall I do for the Sum

Jai

abccells · Feb 25th 2014

Re: unique value 3 columns combined & sum

Excellant. Working like Charm
enhancement on these lines
1. Insert Serial numbers column "A"
2. Remove column "to check unique value :-)" as this is done only to sort the data (1.Bank, 2.ChequeNo and 3.date)
3. Grand total at the end of Data for the column "Amount"

Thanks Mick

Jai

abccells · Feb 24th 2014

Dear experts
i have 'bill1' sheet contains varying number of rows
1. i need to filter on Column "I" as "=Cheque"
2. Copy to "Prn" Sheet (columns G, K, L & M) from 15 Row to Prn sheet as (columns: M as A, L as B, K as C & G as D)
3. The ABC columns together as unique value and sum the data if unique with grand total at the bottom

The code i use to copy and reorder and make the text to number format is very amateur and not complete

HELP ME!

Code

Private Sub Chq()
    Dim rSort As Range
    
    Application.ScreenUpdating = False
    
    Sheets("Prn").Range("A3", Sheets("Prn").Range("g1000").End(xlUp)).ClearContents
    
    With Sheets("bill1")
        With .Range("g14", .Range("g" & .Rows.Count).End(xlUp)).Resize(, 7)
            .AutoFilter Field:=3, Criteria1:="Cheque", Operator:=xlFilterValues
            .Offset(0).Copy
            'AutoFilter Field:=3 means 'The Column from the "G" Column ie. "I" Column
        End With
    End With


    With Sheets("Prn")
        .Range("A1").PasteSpecial Paste:=xlPasteValues, _
            Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False


        Set rSort = .Range("A2", .Range("A" & .Rows.Count).End(xlUp)).Resize(, 6)
        With .Sort
            .SortFields.Clear
            .SortFields.Add Key:=rSort.Cells(1, 5), SortOn:=xlSortOnValues, _
                Order:=xlAscending, DataOption:=xlSortTextAsNumbers
            .SetRange rSort
            .Header = xlNo
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
                    Range("A1:G1000").Select
                    ActiveWorkbook.Worksheets("Prn").Sort.SortFields.Clear
                    ActiveWorkbook.Worksheets("Prn").Sort.SortFields.Add Key:=Range("G2:G1000"), _
                        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
                    ActiveWorkbook.Worksheets("Prn").Sort.SortFields.Add Key:=Range("E2:E1000"), _
                        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
                        xlSortTextAsNumbers
                    ActiveWorkbook.Worksheets("Prn").Sort.SortFields.Add Key:=Range("A2:A1000"), _
                        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
                        xlSortTextAsNumbers
                    With ActiveWorkbook.Worksheets("Prn").Sort
                        .SetRange Range("A1:G1000")
                        .Header = xlYes
                        .MatchCase = False
                        .Orientation = xlTopToBottom
                        .SortMethod = xlPinYin
                        .Apply
                    End With
        '--------------------------------------------
            Columns("G:G").Select
            Selection.Cut
            Columns("A:A").Select
            Selection.Insert Shift:=xlToRight
            Columns("F:G").Select
            Selection.Cut
            Columns("B:B").Select
            Selection.Insert Shift:=xlToRight
            Columns("E:H").Select
            Selection.ClearContents
        '--------------------------------------------
            Cells.Select
            Range("A1").Activate
            Cells.EntireColumn.AutoFit
            Range("B2").Select
            ActiveWindow.FreezePanes = True
        '--------------------------------------------


            
            Range("d2", Sheets("Prn").Range("d1000").End(xlUp)).Select
                For Each xCell In Selection
                    xCell.Value = CDec(xCell.Value)
                Next xCell
            
                Columns("D:D").Select
                Selection.NumberFormat = "#,##0.00"
                Range("A1:D1000").Select
                Selection.Font.Bold = False
              
                Rows("1:1").Select
                Selection.Font.Bold = True
        '--------------------------------------------
        End With
    End With
    
    Sheets("bill1").AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

Display More

abccells · Aug 16th 2008

Re: Move & Transpose Columns to Rows

TNX

abccells · Aug 16th 2008

Re: Move & Transpose Columns to Rows

IT WORKS WELL
But
1. the pasting to the first empty cell in 'D' Coloumn (It may be D6,D7,D8, or D501)
2. C6:C8 may vary (as I have to paste 3 rows of data more than 500 times from Java Web page (It may be C16:C18. can it be last 3 rows in C column?)

Thanking you !

abccells · Aug 16th 2008

Dear Experts
We have to copy from lot of Java webpages
1. Data of single column 3 rows of data to First blank cell in 'C' column (say C6)
2. Then Transpose it across to the same row into 3 columns (from same C6 to d6 & e6)
Here I tried some VBA but needs how to put it across.

Code

Sub Jay()
    Dim iRow As Long, lLoop As Long
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
    DisplayAsIcon:=False
    Range(ActiveCell, ActiveCell.Offset(i, 0)).Copy
    Range("D6").Select ' Select next cell in 'D' coloumn
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Range("C6:C8").Select
    Application.CutCopyMode = False
    Selection.ClearContents
End Sub

Display More

abccells · Aug 12th 2008

Re: Find Column Values Between 2 Files & Copy Paste Into Another

ERROR: 'Next i' IS WITHOUT 'FOR'

Jai

abccells · Jun 4th 2007

Re: Theft Prevention

Dear Reafidy & norie,
thanks for the replies.

As the VBA code needs protection (not the data in it), I thought it will be useful to check the some unique thing in the specific computer. (which can be a file specifically put in for that purpose)
if the user disables macros, the VBA willn't work.

thanks for the efforts u r taking....

jai

abccells · Jun 4th 2007

Re: Theft Prevention

sir
I want to protect my excel workbooks
on opneing it, vba code must check for its associate file (may be placed in windows system folder by me)
on nonexistence it must close with a warning message
i hope that it will prevent unauthorised copying of the excel wbook

hope i have made my intension clear

jai

abccells · Jun 3rd 2007

Dear Gurus,
I want to prevent any Excel Workbook Theft through VBA
1. check the existence of any file in Windows directory ( which we do put for this security)
2. If it doesnt exist "pireted copy? close!" error message may apear

I know it is not fool proof. but it can gives 40% protection

JAI

abccells · May 23rd 2007

Re: Lakh Separator

in India lakh is numerical value for 100,000
So everyone use it as 1,00,000 not as 100,000 (one hundred thousands)
another value crore would mean 10,000,000
but it is used as 1,00,00,000 (one hundred lakhs)

hope i made u clear

jai

abccells · May 23rd 2007

Re: Lakh Separator

my god!
krishna u r a real genius!
marvelous!
krishna nee begane!

jai

abccells · May 22nd 2007

Re: Find First empty Cell

a final working one is found
Anybody here to sugest any improvement....

Code

Sub Roy()
    Dim iRow As Long ' added by zimitry
    ActiveSheet.PasteSpecial Format:="Unicode Text", Link:=False, _
        DisplayAsIcon:=False
    Selection.Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers
    iRow = Range("A65536").End(xlUp).Offset(-1, 0).Row ' added zimitry
    Range(Cells(iRow, 1), Cells(iRow, 1).Offset(2, 0)).EntireRow.Delete xlShiftUp ' added zimitry
    Selection.End(xlDown).Offset(1, 0).Select
End Sub

Display More

thanks gurus (mr.Roy and Zimitry)

abccells · May 19th 2007

Re: Find First empty Cell

Do u HAVE ANY CLUE SIR?

abccells · May 18th 2007

Re: Java Page Web Query

Please help me

abccells · May 18th 2007

Re: Java Page Web Query

anyone here?

abccells · May 18th 2007

Re: Import Data via Web Query

Dear iwrk4dedpr
Is it possible to trace the data from .jsp web page?

jai

abccells · May 18th 2007

Re: Find First empty Cell

dear zimitry
ur code
1. works only if some entries already exists in 'A' column
2. deletes already exiting and replace with new pasting
3. ends two rows below the last row
it stops for 'debug' here

Code

iRow = Range("A65536").End(xlUp).Offset(-2, 0).Row

this following code works with few problems

Code

Sub Roy_2()
    Dim iRow As Long ' added by zimitry
    If Len(Range("a1")) > 0 Then
         Range("a1").End(xlDown).Offset(1, 0).PasteSpecial
    Else
         Range("a1").PasteSpecial
    End If
    ActiveSheet.Paste
    With Selection
        .Copy
        .PasteSpecial
    End With
    Application.CutCopyMode = False
    Selection.SOrt Key1:=ActiveCell, Order1:=xlAscending, Header:=xlGuess, _
        OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortTextAsNumbers
    Selection.End(xlDown).Offset(1, 0).Select
    iRow = Range("A65536").End(xlUp).Offset(-1, 0).Row ' added zimitry
    Range(Cells(iRow, 1), Cells(iRow, 1).Offset(2, 0)).EntireRow.Delete xlShiftUp ' added zimitry
End Sub

Display More

this one works
1. But i dont know why it is slow...
2. why it stops two rows below the last row
3. Is there anyway to use paste special in 'unicode' format?
a refined fast running code is more helpful
thank you gurus....

jai

Posts by abccells

Transpose into new row in next page

Transpose into new row in next page

unique value 3 columns combined & sum

unique value 3 columns combined & sum

unique value 3 columns combined & sum

Move & Transpose Columns to Rows

Move & Transpose Columns to Rows

Move & Transpose Columns to Rows

Find Column Values Between 2 Files & Copy Paste Into Another

Theft Prevention

Theft Prevention

Theft Prevention

Lakh Separator. Indian Rupee Cell Format

Lakh Separator. Indian Rupee Cell Format

Find First empty Cell

Find First empty Cell

Java Page Web Query

Java Page Web Query

Import Data via Web Query

Find First empty Cell