Posts by hgus393

Re: Row Delete by Countif function

Quote from norie

How does it not work?

Well it does not do anything..ie nothing happens to the rows!

Robert

Hello,

I am trying to remove rows if a certain condition is met. More specifically if the row has no value ie only zero the the row should be removed. I am using the countif function to check if the row only has zeros (and if it does then removing it), in the worksheet the formula looks like this:

COUNTIF(D25:X25;"<>0") ie any cell that renders the amount zero should be removed.

I am toying with the following code but I does not work..

Sub RowDelete()
    
    Dim Lrow As Long
    Dim i As Long
    Dim Data As Range
    
    Set Data = Range("D2:Y4595")
       
    Lrow = Data.Rows.Count
    Lrow = Lrow + Data.Row - 1
    Application.ScreenUpdating = False
    For i = Lrow To 1 Step -1
        If Application.WorksheetFunction.CountIf(Rows(i), "<>0") = 0 Then
        Rows(i).Delete
        End If
    Next i
    Application.ScreenUpdating = True
End Sub

Anyone has a clue what I am doing wrong?

Re: Reopened Vlookup using VBA

Quote from Krishnakumar

Hi,

Try in B2 on MasterSheet and copy down and across,

=VLOOKUP($A2,INDIRECT("'"&TEXT(B$1,"yyyy-mm-dd")&"'!B:C"),2,0)

HTH

Display More

Aha, that works even better!!! Thank you even more :yourock:
Robert

Re: Reopened Vlookup using VBA

Quote from royUK

Just open the original post & copy & paste from your Explorer address box

Thank you! I will do this the next time, sorry for the inconvenience!

Robert

Re: Reopened Vlookup using VBA

Quote from carlmack

Try this

Thank you this works as I would expect it to... :thanx: :thanx:

Robert

Re: Reopened Vlookup using VBA

Quote from royUK

You should include a link to the previous question

Eh, Yes!
But I couldn't find how to do this..How do I do this?
//Robert

Re: Reopened Vlookup using VBA

Ok,

To some extent this had to do with the country setting I have, however I have corrected for this but it still does not work...anybody any clues?

//Robert

Re: Reopened Vlookup using VBA

Quote from h1h

you have to pass the date to the indirect function as text, i.3. indirect(text($a$1;"yyyy-mm-dd") & etc etc

Sorry but this does not work......

I had a post with the following title "Vlookup using VBA". I got help, and it worked out fine in the example I uploaded BUT... The sheet names in my project are named as dates and as such referencing these by the indirect formula I get #ref - does anyone really smart know how to get round this?
I am posting an example file to show you all what I mean.

//Robert

Re: Vlookup using VBA

Yep that did it, thank you very muc for your help!!
:thanx: :thanx:

Re: Vlookup using VBA

Alright I am posting an example that will hopefully help you understand what I am trying to do. The sheet names are dynamic and as such I cannot use a simple formula for this, the users are not experienced users of excel and will therefore not be able to correct this themselves. The example consists of 11 sheets and it is the master sheet where I would like to have things done. Ie a macro (or formula if it is possible) that would create a vlookup irrespective of the dynamicity of the sheet names. Hope you understand what I mean!

Hello all...again!

Does anyone know how to do the following?

I am starting out with a master sheet that contains account numbers, I got help from Wigi ( :thanx: ) to name the columns the same as the worksheets in the workbook.

What I am trying to do is create a vba that will do a vlookup from the master sheet starting with sheet1 ending with the penultimate sheet and for every new sheet the vlookup formula in the master sheet should move one column to the right, to correspond to the new worksheet.

Doing a simple record macro of the vlookup formula renders the following:

Sub Analysis()
    ActiveCell.FormulaR1C1 = "=VLOOKUP(C[-2],'Sheet1'!C[-1]:C[6],8,FALSE)"
End Sub

The activecell in the formula above is the first empty cell in column C (column C corresponds to the first sheet, column D corresponds to second sheet etc.)

The actual number of cells to be looked up in the master sheet is 4595 rows.

//Robert

Re: Column name same as sheet name

:thanx:
That worked really well! Thank you!!

Hello,

I am trying to create vba that puts the sheet name for all the sheets in the workbook (except for the last two) into the last worksheet ranging from the first empty column in row 1 onwards (ie dependent on how many sheets there are in the workbook). Does anyone have an idea how to do this? I could not find anything in the previous posts how to do this.

In the last workbook I am choosing the first empty column by the following:
and this is how far I have gotten....

Sub ColumnNames()
Dim NextColumn As Integer
NextColumn = Range("IV1").End(xlToLeft).Column + 1

//Robert

Re: Adding rows based on a range

:thanx:

Splendid!! I works like a charm!

Thank you!!!

Re: Find and Populate cells with user input

What are you trying to do?
Do you want a form that populates the first empty cell in column A (Work order) and then populates the first empty empty cell in column B (Location)?

Hello all,

I'm stuck on a problem! I have a range from (j8 to j28) where some cells have information and some don't. What I am trying to do is copy the cells (in range j8 to j28) that have a value, ignoring the ones that don't have a value, to the next empty cell in the B column - and it does not work for me, what am I doing wrong? The code I am using is the following:

Sub Notes()
Dim NextRow As Double
Dim Notes As Range
NextRow = Range("B65536").End(xlUp).Row + 1
For Each Notes In Worksheets("Sheet1").Range("j8:j28").Cells
    Cells(NextRow, 2).Value = Notes.Value
    Next Notes
End Sub

Grateful for any help!!

Re: Eliminating null values

:thanx:
That works a lot better, Smart using the used cell as the starting point, never thought of doing this...The more I learn the more I understand that I don't know much :?
:thanx:
Robert

Re: Eliminating null values

Still does not work!

Sub Data
Range("a1",Range("65536")).End(xlUp)
Sub

I added a ) as it was complaining about this but running this I get "Invalid Use of Property"
?Robert

Re: Eliminating null values

Hum,

I get the following messages when trying to use your code:

Compile error
Expected =