Posts by murthysri272

Re: Copying the data based on the cells availability and Loop it

Hi,
Sorry for the late response.

Using the attached file data, I have do the below actions:

1. Filter "Column19" on "AGENTorDRM"
2. Filter "Column8" on "KAX0"
3. Again Filter "Column18" on "0"
4. Then Copy Column H, P, X and copy it to new sheet called "Agent&DRM"
5. Replace “KAX0” to “AAX0”

Do the above steps from KAX0 to KAX9 to gather the data to get the required info. At the end of the loop should have all data for "AGENTorDRM", KAX0 to KAX9 (AAX0 to AAX9 – as we are renaming it) and indicator “0”

In my first message, I tried the little coding part for this scenario. Please help me to get the coding for the above scenario using looping.

Please let me know if you require any additional information on this scenario.

Regards,
Sri

Re: VBA Code for scenario

Hi Max,
Its working fine. Thank you,

Regards,
Sri

Hi,
Could you please help me VBA coding for the scenario in the attached picture.

[ATTACH=CONFIG]68708[/ATTACH]

Regards,
Sri

Re: VBA Code: sum the values based on unique values

Hi,

Anyone could you please help me to accommodate this scenario.

Regards,
Sri

Re: Copying the data based on the cells availability and Loop it

Hi,

Anyone could you please help me to accommodate this scenario.

Regards,
Sri

Hi,
I am working a scenario. which is " filter specific combination in sheet A and paste in Sheet B of cell A1 and for next loop data should copy available rows of Column A5000 (From the available cells in the previous step) and looping continues until it is complete.

Please find my code below:

I am looking some coding suggestion in ('Comment places)

Sub IdentifyAgentAAX0to9()
    Dim Rng As Range, A As Variant, e As Variant, sht As Worksheet, n As Long, i As Long, j As Long
    n = 10: i = 0: j = 1
    With Sheets("AMC Agent Breakout")
        .Activate
        For Each e In Array("KAX0", "KAX1", "KAX2", "KAX3", "KAX4", "KAX5", "KAX6", "KAX7", "KAX8", "KAX9")
            .Range("A1:Z" & .Range("A" & .Rows.Count).End(xlUp).Row).AutoFilter Field:=8, Criteria1:=e
            .Range("A1:Z" & .Range("A" & .Rows.Count).End(xlUp).Row).AutoFilter Field:=18, Criteria1:="0"
            Set Rng = .AutoFilter.Range.Offset(1, 0).Resize(.AutoFilter.Range.Rows.Count - 1, .AutoFilter.Range.Columns.Count) _
            .SpecialCells(xlCellTypeVisible)
                Range("H:H,P:P,X:X").Select
                Range("X1").Activate
                Selection.Copy
                Sheets("Agent&DRM").Select
                Cells("A", j).Select ' Each and every time copy location should change based on the available space
                ActiveSheet.Paste
                'replace KAX(i) to AAX(i)
                Selection.Replace What:="KAX" & i, Replacement:="AAX" & i, LookAt:=xlPart, _
                    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                    ReplaceFormat:=False
            n = n + 1: i = i + 1: j = j + 5000
            .AutoFilterMode = False
        Next
    End With
End Sub

Please let me know if you need any further clarifications on this..

Regards,
Sri

Hi,
Please find the attached sample data file and I am looking for sample VBA code for below scenario.

My result should display in Column D and I need a sum of values in ColumnB which should come from unique combination of ColumnA and ColumnB.

Example from data file: My result ( ColumnD), For the combination of "AM02" (ColumnA) and "disp_AC_In" (ColumnC) should be 350.

I hope you understood my scenario. Please let me know if you require more details to understand my scenario.

Regards,
Sri

Re: Help in the coding part

I have resolved this issue by myself. Thank you. Apologies for the inconvenience caused.

Hi,
I have 2 columns (A,B) in a sheet1. I am filtering data on Column A and I can see 2 values (AA,CC).
When I filter data on column A for AA, sum is coming as 32 (Which is display on cell T1)
When I filter the data on Column A for BB, sum is coming as 0 , But the same 32 is displaying in Cell T1.
Again, when I filter data on column A for CC, sum is coming as 88 (Which is display on cell T1 and is correct)
Finally, When I filter the data on Column A for DD, sum is coming as 0 , But the same 88 is displaying in Cell T1.

Data for BB, DD is available in few times only.

Please let me know how I can get the SUM as "0" when I filter data for BB and DD.

I am looking for VBA code for the below scenario.

Thanks in advance.

Regards,
Sri

Hi,
In the below attached code (): When I am filtering data on KAX2, Even though it has no KAX2 data and it has already existing data for KAX1 , it is providing the same sum as KAX1.

Please help me on this.

Sub Test()
    Dim Rng As Range, A As Variant, e As Variant, sht As Worksheet, n As Long, i As Long
     
    On Error Resume Next
    Set sht = ThisWorkbook.Sheets("Dispatch_load")
    If sht Is Nothing Then
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Dispatch_load"
    End If
     
    n = 10: i = 0
    With Sheets("AMC Agent Breakout")
        .Activate
        For Each e In Array("KAX0", "KAX1", "KAX2", "KAX3", "KAX4", "KAX5", "KAX6", "KAX7", "KAX8", "KAX9")
            .Range("A1:R" & .Range("A" & .Rows.Count).End(xlUp).Row).AutoFilter Field:=8, Criteria1:=e
            .Range("A1:R" & .Range("A" & .Rows.Count).End(xlUp).Row).AutoFilter Field:=18, Criteria1:="0"
            Set Rng = .AutoFilter.Range.Offset(1, 0).Resize(.AutoFilter.Range.Rows.Count - 1, .AutoFilter.Range.Columns.Count) _
            .SpecialCells(xlCellTypeVisible)
             
            If Not Rng Is Nothing Then
                On Error Resume Next
                Set sht = ThisWorkbook.Sheets("Agent&DRM")
                If sht Is Nothing Then
                    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Agent&DRM"
                End If
                With Sheets("Agent&DRM")
                    .Activate
                    Rng.Copy .Range("C6")
                    .Columns("C:I").Delete Shift:=xlToLeft
                    .Columns("D:J").Delete Shift:=xlToLeft
                        Range("C5").Select
                        ActiveCell.FormulaR1C1 = "KCODE"
                        Range("D5").Select
                        ActiveCell.FormulaR1C1 = "Count"
                    .Columns("E:F").Delete Shift:=xlToLeft
                    .Range("C5:D5" & .Range("C" & .Rows.Count).End(xlUp).Row).AutoFilter Field:=1, Criteria1:=e
                    .Range("T1").FormulaR1C1 = "AAX" & i
                         Range("U1").Select
                         ActiveCell.FormulaR1C1 = "=SUM(R[5]C[-17]:R[2779]C[-17])"
                    .Range("T1:U1").Copy
                    Sheets("Dispatch_load").Range("A" & n).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
                End With
            End If
            n = n + 1: i = i + 1
            .AutoFilterMode = False
        Next
    End With
End Sub

Re: VBA Code: excel is hanging at some point

Hi,
First Function of the code (Test) is working fine. But When I am a particular filtering the combination (KAX7,0 and KAX9,0) of data data sum of all the rows should come as zero. Because as there is no data for KAX7 and KAX9. But I am getting KAX6 sum to KAX7 and KAX8 sum to KAX9. I think, I just need to clear the cells before next combination is getting added and Copied over. Please suggest me on this.

Thank you so much for your help.

Regards,
Sri

Hi,
While I am executing the attached script, excel is hanging at some point (Data For AAX7 / Data For AAX8). Please let me know what can I do to avoid this scenario. Your help on this issues is much appreciated.

I think this issue is happening at the time of copying the data after filtering. It looks like, It has lot many rows to copy and paste; may be that's why it is hanging. More over when I am executing the each line step by step I am not getting any issue.

Regards,
Sri

Re: VBA Code: ascending order of the columns

Hi,
Its working fine.

I just have a question in below scenario:

I have remove some combination of data (KAX0, KAX1, -,-,-,-,KAX9)from my sheet. Scenario is as follows:

Input:

ColumnA ColumnB
KAA3 5
KAA5 55
KAX0 4
KAX0 3
KAX0 8
KAX0 21
KAX1 2
KAX1 98
KAX2 2
KAX2 67
KAX2 876
KAX3 8
KAX3 8
KAX3 43
---
----
---
---
KAX9 9
KAX9 8
KAX9 11
KAX9 12
KA33 45
KA54 8

Output:

KAA3 5
KAA5 55
KA33 45
KA54 8

Regards,
Sri

Re: VBA Code: ascending order of the columns

Hi,
Its working fine. Thanks for your help

Regards,
Sri

Re: VBA Code: ascending order of the columns

Could you please explain more about below point?

Create SUMIF formula new to the condensed list

Re: VBA Code: ascending order of the columns

Hi,
I am trying to accomplish this scenario in VBA coding. is it possible to do so?

Regards,
Sri

Re: VBA Code: ascending order of the columns

Okay, I got it.
Thanks for your suggestion.

After sort My data (INPUT) is as below, is there anyway I can get my result (OUTPUT) using VBA Macros? If it is feasible please help me with sample code.

Input:

Column A Column B

AMJ0 $34
AMJ0 $99

CBHY $14.0
CBHY $73.0

CT00 $12

KALA $58
KALA $76

KAZO $123.7
KAZO $13.0

KA55 $28.5
KA55 $23.5

KL76 $34

Output:

AMJ0 $133

CBHY $87.0

CT00 $12

KALA $134

KAZO $136.7

KA55 $52.0

KL76 $34

Regards,
Sri

Hi,
I have a spreadsheet which is having Column A to B. I want to sort the column A as alphabetical order (So cells which are having same values should come together), so all columns should adjust with that accordingly.

can we able to achieve this scenario in VBA code? If yes, please provide me the sample code.

Sample data for the scenario is as below:

Column A Column B
KALA $58
KAZO $123.7
KA55 $23.5
CBHY $14.0
AMJ0 $99
CBHY $73.0
AMJ0 $34
KAZO $13.0
KA55 $28.5
CT00 $12
KALA $76
KL76 $34

Regards,
Sri

Re: VBA Code: Auto Filling Option

Its working good.

Thank You,

Regards,
Sri

Hi,
From the below code, the highlighted code is auto filling the cells from G6 to G511 and I am hardcoding the values.

Instead of that is there anyway I can apply Ctl+Shift+Down arrow Function?

or

Is there anyway same formula in G6 can be applied to the no.of cells based on data present in its adjacent column (F)

Range("G6").Select ActiveCell.FormulaR1C1 = "=HPLNK(RC4,RC1,R1C,RC6,R2C,R3C,R4C)"
Selection.AutoFill Destination:=Range("G6:G511")
Range("G6:G511").Select

Regards,
Sri