Posts by haroon2015

Re: Vba code is taking too much time to do job

KjBox

will u plz add those lines in my code?

because, where i put those line i am confuse.

I am using below code to copy paste data in same workbook

But it taking too much time

Sub OLV_FORMULA_COPY()
Sheets("OL V").Activate 
    Application.ScreenUpdating = False 
    Dim i, j, count 
    count = WorksheetFunction.CountIf(Sheets("OL V").Range("W4:W53"), ">0") - 1 
     
     
    i = 47 
    For j = 1 To count 
        Range("A4:R46").Copy 
        Range("A" & i).PasteSpecial Paste:=xlPasteAll 
        i = i + 43 
    Next j 
    Application.ScreenUpdating = True 
End Sub

Re: Copy and paste is very slow VBA

KjBox
in below code i am having same problem, will u look plz this one here or i start a new thread?

Sub OLV_FORMULA_COPY()Sheets("OL V").Activate
Application.ScreenUpdating = False
Dim i, j, count
count = WorksheetFunction.CountIf(Sheets("OL V").Range("W4:W53"), ">0") - 1




i = 47
For j = 1 To count
    Range("A4:R46").Copy
    Range("A" & i).PasteSpecial Paste:=xlPasteAll
    i = i + 43
Next j
Application.ScreenUpdating = True
End Sub

Re: Copy and paste is very slow VBA

why i am not able to add reputation you u
@KjBox

Re: Copy and paste is very slow VBA

@KjBox
THANX A LOT

I CHANGED THESE ONES

[/I][COLOR=#417394]        End With[/COLOR][COLOR=#417394]        Exit Sub[/COLOR][COLOR=#417394]End With[/COLOR][COLOR=#417394]End If[/COLOR][COLOR=#417394]End Sub[/COLOR][I]

NOW ITS WORKING PERFECTLY AS I WANTED[/I]

Re: Copy and paste is very slow VBA

giving an error 438
object doesn't supoort property
at below line

[COLOR=#333333].Cells(.Rows.Count, 4).End(xlUp).Offset(1).Resize([/COLOR][COLOR=blue]UBound[/COLOR][COLOR=#333333](x1, 1)) = x1[/COLOR]

Re: Copy and paste is very slow VBA

not working

Re: Edit each cells but only unique one using vba

leave this thread,

i am happy with inputbox code,

thanx for your great help

hi,

i am using this code to copy data and paste at other workbook,
but it is very slow.
need help

Sub ExcelToTally_V2()Dim r As Range
Set r = Sheet15.Range("D3")
If r.Value > 0 Then
Dim sh1 As Worksheet
Set sh1 = ActiveWorkbook.Worksheets("V2")
Windows("Vouchers - Purchase Transactions With StockItems.xlsm").Activate
On Error Resume Next
With sh1
   .Range("C5:C54").SpecialCells(xlCellTypeVisible).Copy
   Range("D" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
   .Range("E5:E54").SpecialCells(xlCellTypeVisible).Copy
   Range("F" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
   .Range("F5:F54").SpecialCells(xlCellTypeVisible).Copy
   Range("H" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
   .Range("G5:G54").SpecialCells(xlCellTypeVisible).Copy
   Range("J" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
   .Range("H5:H54").SpecialCells(xlCellTypeVisible).Copy
   Range("E" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
   .Range("I5:I54").SpecialCells(xlCellTypeVisible).Copy
   Range("C" & Rows.count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteValues
End With
Else
Exit Sub
End If

Re: Edit each cells but only unique one using vba

Because i need to edit a cell

it activate a another macro ( Private Sub Worksheet_Change(ByVal Target As Range) )

edit means just like u select a cell and just press f2

Re: Edit each cells but only unique one using vba

Hi, kjbox
as u know my range is c10:c300, ok

which cells i want to added using macro?

Quote

one by one, c10 to last added data

in above under quote line means
i add data to c10, then c11, then 12, then 13..........to 30
now code only added cells c10 to c30

if i have data in range c10:c50
then edit only c10 to c50

let me know if you want to know more about my requirement

Re: Edit each cells but only unique one using vba

one by one c10 to last added data

Re: Edit each cells but only unique one using vba

Sub Haroon2015_RangeEditing()
Dim rCell As Range, vAns As Variant
Redim x(1 To 1)

On Error Goto ErrHndlr 'check there is at least one cell in c10:c300 with a value
[c10:c300].SpecialCells(2).Select
For Each rCell In Selection
If IsError(Application.Match(rCell, x, 0)) Then
vAns = InputBox("Edit cell if wanted", "Edit cell ", rCell)
Redim Preserve x(1 To UBound(x) + 1)
x(UBound(x)) = rCell
If Not vAns = "" Then rCell = vAns
End If
Next
ErrHndlr:

End Sub

I AM USING THIS CODE

Re: Edit each cells but only unique one using vba

Ok mod
plz do it

Re: Edit each cells but only unique one using vba

mod,
i have a another question to related this thread,

is it possible to edit unique range without inputbox?
i mean when i run the code it edit unique cell without showing inputbox

or should i start a new thread?

Re: Edit each cells but only unique one using vba

Thanx a lot,
Mod
you saved my lots of time....

hi, everyone
i am using below code to edit every cell in range.("C10:C300"),
but
now its checking every cell and edit him,
but
i want it edit only unique data,
for example -

cell 10 have data = haroon, then edit it
cell 11 have data = alan, then edit it
cell 12 have data = haroon, the skip it

Sub Haroon2015_RangeEditing()


If WorksheetFunction.CountA(Range("C10:C300")) = 0 Then Exit Sub




Range("C10:C300").SpecialCells(xlCellTypeConstants).Select
Dim rngCell As Range
Dim varAns




For Each rngCell In Selection
  varAns = InputBox(prompt:="Edit cell if wanted", Title:="Edit cell ", Default:=rngCell.Value)
  If Not varAns = "" Then rngCell.Value = varAns
Next rngCell
Range("C10").Select




End Sub

thanx in advance

Re: Center UserForm On Screen On Show

thanx [Blocked Image: http://www.ozgrid.com/forum/images/avatars/OzGrid.BusApp.170x45.gif] Dave Hawley

Re: Userform find, edit and update data error

here is right code to work Solved By Sixthsense

Private Sub ComboBox1_Change()Dim v As Variant, r As Range, vValue As Variant, i As ByteSet r = Sheet4.Range("D61:D500")vValue = ComboBox1.ValueIf IsNumeric(vValue) Then vValue = CDbl(vValue)v = Application.Match(vValue, r, False)    If Not IsError(v) Then        TextBox1.Value = r.Cells(v).Offset(0, -1).Value        TextBox2.Value = r.Cells(v).Offset(0, -3).Value        TextBox3.Value = r.Cells(v).Offset(0, 0).Value        TextBox4.Value = r.Cells(v).Offset(0, 1).Value        TextBox5.Value = r.Cells(v).Offset(0, 2).Value        TextBox6.Value = r.Cells(v).Offset(0, 3).Value        'MsgBox "Now you can edit."    End IfEnd Sub

Private Sub CommandButton2_Click()Dim v As Variant, r As Range, vValue As Variant, i As ByteSet r = Sheet4.Range("D61:D500")vValue = ComboBox1.ValueIf IsNumeric(vValue) Then vValue = CDbl(vValue)v = Application.Match(vValue, r, False)If IsNumeric(v) Then    With r.Cells(v)        .Offset(, -1).Value = TextBox1.Value        .Offset(, -3).Value = TextBox2.Value        .Offset(, 0).Value = TextBox3.Value        .Offset(, 1).Value = TextBox4.Value        .Offset(, 2).Value = TextBox5.Value        .Offset(, 3).Value = TextBox6.Value    End With             For i = 1 To 6        Controls("TextBox" & i).Value = ""    Next i        ComboBox1.Value = ""    'MsgBox "successful edited."End IfEnd Sub

Re: Userform find, edit and update data error

ok royUK