Posts by tjrt

tjrt · Jul 14th 2016

Re: Object Variable or With Block Variable not set. Quite confused here.:(

Hi norie,

Haste makes waste.

Quote

What are you trying to do?

Quote

And now it seems to work as I want.

But I was wrong. Please could you help me with the correct syntax to Exit the Sub if the cursor is not within the Range A2:A10?

Thank you norie!

tjrt · Jul 14th 2016

Re: Object Variable or With Block Variable not set. Quite confused here.:(

Thanks for the quick reply, Flash. I mean, norie.

Wow, I am an idiot! You are correct!

Quote

What are you trying to do?

In Column A2:A10 if any of those cells value changes either Hide or Show Userform1. Values in that range are already in place and depending which value was selected respond accordingly.

I have changed your code in post no. 4 to,

Code

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim MyObject As Object ' Create object variable.
     
    Set MyObject = UserForm1
     
    Application.EnableEvents = False
     
    If Application.Intersect(Target, Range("A2:A10")) Is Nothing Then Exit Sub
     
    If Target.Value <> "" Then
        Sheet1.Shapes("Button 1").Visible = msoFalse
        Sheet3.Range("C8:C9").ClearContents
        MyObject.Show
    End If
     
    Application.EnableEvents = True
     
End Sub

Display More

And now it seems to work as I want.

Thank you norie!

tjrt · Jul 14th 2016

Re: Object Variable or With Block Variable not set. Quite confused here.:(

Thanks for the quick reply, Flash. I mean, norie.

Wow, I am an idiot! You are correct!

Quote

What are you trying to do?

In Column A2:A10 if any of those cells value changes either Hide or Show Userform1. Values in that range are already in place and depending which value was selected respond accordingly.

tjrt · Jul 14th 2016

Re: Fix loop for cell reference totally stuck

Working with Tables sometimes require different methods. Can you supply a working sample of your Workbook?

tjrt · Jul 14th 2016

Hi to all of you,

Could anyone please direct me in the right direction and tell me what is wrong with my code. It does not work. Feel free to call me an idiot.! LOL

Code

Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyObject As Object    ' Create object variable.
Set MyObject = UserForm2
Application.EnableEvents = False[/TR]
'On Error Resume Next
If Not Application.Intersect(Range("A2"), Range("A10")) Is Nothing Then Exit Sub
'On Error Resume Next
[COLOR=#ff0000]If Target.Application.Intersect(Range("A2"), Range("A10")).Value = "" Then  [/COLOR][COLOR=#0000cd]' the procedure stops here[/COLOR][COLOR=#ff0000][/COLOR][COLOR=#ff0000][/COLOR]
UserForm1.Hide
ElseIf Target.Application.Intersect(Range("A2"), Range("A10")).Value <> "" Then
Sheet1.Shapes("Button 1").Visible = msoFalse
Sheet3.Range("C8:C9").ClearContents
UserForm1.Show
End If


Application.EnableEvents = True


End Sub

Display More

Any help would be most welcome,

Thanks.

tjrt · Mar 9th 2014

Re: Find Blank Cells in a Column and then input a Value

@ apo,

Yes you are correct, but if there would be an empty row between the Header Row and the Data set, the code does not work. this scenario would aslo apply to jindon's Code, where it returns a value in any Blank Cell within the CurrentRegion, from the Cell above, and also adds an Extra Row at the bottom of the Data Set.

I am merely attempting to tweak the Code to cater for an empty row below the Headers in Row 1.
It also fails if there should happen to be an empty row within the Data Set. As soon as the Code reaches the first empty Row within the Data set, it stops right there.:(

Thank you for your interest guys!:)

tjrt · Mar 8th 2014

Re: Find Blank Cells in a Column and then input a Value

@ royUK,

Maybe you meant to have your code as follow?:)

Code

Option Explicit
 
Sub FillBlanks()
    Dim rC1 As Range
     
    For Each rC1 In ActiveSheet.Range([COLOR=#ff0000]"C1"[/COLOR]).CurrentRegion.Columns(3).Cells
        If IsEmpty(rC1) Then rC3.Value = rC1.Offset(-1).Value
    Next rC1
End Sub

Till Later.

tjrt · Feb 5th 2014

Re: Code for finding duplicates to ignore empty Cells

@ [Blocked Image: http://www.ozgrid.com/forum/image.php?u=115597&dateline=1300466393]smuzoen,

Hello smuzoen,

I cannot thank you enough for your valuable contribution.

With the help from you guys, this is what I finally ended up with, and if I may add, It works just GREAT!!!

This Forum ROCKS!!! :cheers:

tjrt · Feb 5th 2014

Re: Code for finding duplicates to ignore empty Cells

@ [Blocked Image: http://www.ozgrid.com/forum/image.php?u=115597&dateline=1300466393] smuzoen,

Thank you for your contribution. It seems that we have posted our replies at the same instant.

In my first post of this Thread, I have posted a link to a thread dealing with not only clearing duplicates, but also the original duplicate value.

I have tried to implement your alternative options, without success. If you click on the "Delete Duplicates" button a second time round, it messes up the actual data.

Thank you.:)

tjrt · Feb 5th 2014

Re: Code for finding duplicates to ignore empty Cells

@ KjBox,

Thank you for your suggestion. In the attached sample Workbook, you will see that it works 100%, with the "Delete Duplicates" Button, on the first click only.:(

If you click it again, it seems to regard empty Cells in the range A to C as "duplicates", and deletes data that should remain.

@ snb,

In the same attached sample Workbook, I have added your proposal, and a Button called "SNB". Because I have "Option Explicit" at the top of my Code, I had to add some Dim statements to your Code. It returns a vertical message box, too large for the screen. It does not delete duplicates. I am afraid I do not fully understand what your suggestion is supposed to do.:(

Thank you guys!:)

tjrt · Feb 5th 2014

Hello all,

In this Thread Jindon has provided a sample Workbook, with some fantastic Code.

http://www.ozgrid.com/forum/showthread.php?t=162443

And here is the Code:

Code

Sub Test()
    Dim r As Range, w, e, txt As String
    
    With CreateObject("Scripting.Dictionary")
        .CompareMode = 1
        For Each r In Range("a1").CurrentRegion
            If Not .exists(r.Value) Then
                .Item(r.Value) = VBA.Array(False, r.Address)
            Else
                w = .Item(r.Value)
                w(0) = True
                .Item(r.Value) = w
            End If
        Next
        For Each e In .keys
            If .Item(e)(0) Then
                txt = txt & "," & .Item(e)(1)
                If Len(txt) > 240 Then
                    Range(Mid$(txt, 2)).ClearContents
                txt = ""
                End If
            End If
        Next
    End With
    If Len(txt) Then Range(Mid$(txt, 2)).ClearContents
End Sub

Display More

How would one tweak this Code to ignore empty cells, so that it does not consider empty cells as duplicates?

Thank you.

tjrt · Jun 7th 2013

Re: Automate a recorded Macro to shorter Code to hadle variable Ranges

Hello StephenR,

Brilliant, thank you so much!;)

I really appreciate your time and help, and you make it look so easy!

tjrt · Jun 7th 2013

Re: Automate a recorded Macro to shorter Code to hadle variable Ranges

Hello cytop,

Thank you for your response,

Quote

Some users cannot, or will not, download attachments.

Sorry, I was not aware of that, in this Forum.

I would appreciate some help as how to automate the below recorded Macro in such a way that it goes through each row, remove duplicates, and then continue to the last row within the range, which may vary from time to time.:-(

Code

Sub Macro1()


    Application.ScreenUpdating = False
    Range("B2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("D2").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Application.CutCopyMode = False
    ActiveSheet.Range("$D$2:$D$20").RemoveDuplicates Columns:=1, Header:=xlNo
    Selection.Copy
    Sheets("Sheet1").Select
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Sheet2.Columns("D").Clear
        Range("B3").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("D2").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Application.CutCopyMode = False
    ActiveSheet.Range("$D$2:$D$20").RemoveDuplicates Columns:=1, Header:=xlNo
    Selection.Copy
    Sheets("Sheet1").Select
    Range("B3").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Sheet2.Columns("D").Clear
        Range("B4").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Copy
    Sheets("Sheet2").Select
    Range("D2").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Application.CutCopyMode = False
    ActiveSheet.Range("$D$2:$D$20").RemoveDuplicates Columns:=1, Header:=xlNo
    Selection.Copy
    Sheets("Sheet1").Select
    Range("B4").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Sheet2.Columns("D").Clear
    Application.ScreenUpdating = True


End Sub

Display More

Once again, Thank you.:)

tjrt · Jun 7th 2013

Hi all,

In the attached WorkBook, I have recorded a Macro which requires some tweaking. I would appreciate some help as how to automate it in such a way that it goes through each row, remove duplicates, and then continue to the last row within the range.:(

Thank you.:)

tjrt · Jan 11th 2013

Re: Copy Shape to new Sheet, only if it does not already exists

Helo cytop,

Once again, thank for your time and response!:)

I have managed to get it working now by incorporating your:

Code

[COLOR=#006400]If sheets(strSelect).Range("H4").Value = vbNullString[/COLOR]

Thank you for the help.;)

tjrt · Jan 9th 2013

Re: Copy Shape to new Sheet, only if it does not already exists

Hello cytop,

Code

'// Check if any images on destination sheet
            If .Shapes.Count = 0 Then 
                 
                 '// No - so copy
                Sheets("Parts 1").Shapes("Curved Left Arrow 1").Copy 
                 
                .Range("H2").Select 
                .Paste 
                .Range("H4").Value = "Go Back" 
                 
                 '// Image will be selected so move cursor away ...
                .Range("A1").Select 
                 
            End If

Display More

If any Sheet happens to have any other Shape on it the, Shapes("Curved Left Arrow 1") does not copy. I need to find a way to work with Shapes Names, i.e. If a Sheet does not contain a Shapes("Curved Left Arrow 1"), then paste it, else ignore.

Could you help me with that please?;)

tjrt · Jan 8th 2013

Re: Copy Shape to new Sheet, only if it does not already exists

Hello cytop,

Absolutely AMAZING!:)

This is one of the most BRILLIANT replies and solution to a problem, complete with comments, I have ever received from any Forums alike! Sorry, there just does not exist an "Alike" forum.

Thank you so much for your help!:)

It works FANTASTIC!:dance:

tjrt · Jan 8th 2013

Hi all,

I have a Master Sheet, Sheet1, with a Named Range in Column M which gets updated with a Button called "Update Sheet Names"

On Sheet1 I also have a ComboBox from where I can select to activate any chosen sheet. Objective is, if it is a new sheet which does not already contain a certain Shape, to have that Shape copied to that Sheet, else don't copy it.

Please see the attached WorkBook. I have tried almost anything, but just cannot get it to work.:(

Any help will be greatly appreciated.:)

tjrt · Dec 23rd 2012

Re: Code to find true Sheetname

Hello rdperkins,

I agree with royUK on this.

Also what would happen to the Original Sheet Name if a user changes any Sheet Name more than once or twice?

The PrevShtName will only recall the last Name a Sheet was changed to, discarding the Original Sheet Name.

Would it not be better to keep it short, simple and sweet? As per the revised attached sample WorkBook, for example?:)

tjrt · Dec 22nd 2012

Re: Code to find true Sheetname

I have updated the WorkBook where on Sheet1 in Column A it Lists the Actual Sheet Names, and in Column B it Shows the Sheet Number.

Hope that solves your issue.:)