Posts by dejayajay

I have created a Pivot Table with: Column: "Who" (text), Row: "Task" (text), and Values: "Time Value" (number - derived from a formula, converting time h:m to decimal). The Value is calculating as 'Count of Time Value', (giving the number of entries), but what I need is the Sum, not the Count. In the Pivot Table Fields dialogue, i cannot see the expected option to choose Sum instead of Count. Any ideas why?

(In a previous version of this spreadsheet, the Pivot Table worked OK, giving me the Sum of).

Roy, thank you so much. problem solved immediately. Bless you!

The formulas in the spreadsheet are not updating, IE calculating new results, as I enter new values in source cells. Spreadsheet is a re-named copy of a sheet that does work. Each has a pivot table, for calculating and displaying sums per product. The formulas are basic, IE time difference between 2 cells; convert h:m to decimal value; copy data in another cell (formatted to dd-mmm-yy), formatted to dd; etc). Seems like nothing is working, even if I re-type the formula. ???

Thank you.

I deleted everything in Module 1, and pasted your code in, and it works via Alt+F8 and selecting and running the macro. Still won't work via keyboard shortcut. Now i have the code in both Module 1 and 2, and they both work as described above.

Thank you, Roy. It is working now. I put it into a new module, and now I can run it via Alt+F8 and selecting and running the macro. So that is definitely progress.

I cannot get it to run via the keyboard shortcut though. I went into Module 1 and deleted the existing macro that used the same keyboard shortcut, but that hasn't helped.

Thank you for your help to date. BTW, what educational resource can you suggest for me to study to become proficient at VBA. There are a lot out there of course.

NOTE: I cannot get anywhere with cryptic crosswords. Is it possible that my brain is not wired right to grasp VBA? Or can anyone do it, with enough effort and time?

Sincerely my apologies. Happy to comply, but please, where is this 'Edit' to click on?

Thank you, Roy. I've entered the code as you show, and when executing, I get the message

"'Compile Error:

Syntax Error"

I notice that the line "Dim 1Rws As Long" shows in red.

Also, the first line "Option Explicit" insists on placing itself above what I presume is the horizontal dividing line between the code of this macro and the one above it. Hmmm. Should the line "Option Explicit" be at the very top of our code?

So I have tried placing the lines "Option Explicit" and "Dim 1Rws As Long" below the line "Sub MergeAndLHAlign()". Now I get the message "Invalid inside proceedure". The code now is as follows:

Sub MergeAndLHAlign()    (This line is highlighted yellow)
'


' MergeAndLHAlign Macro
' Merge cells and align Left


' Keyboard Shortcut: Ctrl+Shift+L
'
Option Explicit
Dim 1Rws As Long   (This text is red)
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 1
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
Selection.Offset(1).Select
End Sub

Thank you for replying RoyUK. I appreciate your advice to avoid merging cell, but I am not using them in a part of the spreadsheet where calculations are to take place. IE they are in a place where I have name, address, type stuff. Having said that, following is the contents of the macro that combines the instruction to merge, and align to the left, with an indent. This is to help speed up the process of formatting the appearance of the spreadsheet.

What I am having trouble with is trying to add code to move the focus down to the equivalent cells on the next row down. As mentioned, I have tried to add a line with 'ActiveCell.Offset(1,0).Select' , but it is not working as expected. Can you assist?

Sub MergeAndLHAlign()
' ' MergeAndLHAlign Macro ' Merge cells and align Left
' ' Keyboard Shortcut: Ctrl+Shift+L
    With Selection
        .HorizontalAlignment = xlLeft
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 1
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = True
    End With
End Sub

I am trying to make, and or modify, a macro, to select the next cells down, after merging cells in a row. If I record a macro that moves and selects cells in the next row down, ready for the next Merge command, it will always send me to the same cells each time, EI instead of moving to a new set of 'next cells down'. I have tried to edit the macro, using 'ActiveCell.Offset(1,0).Select' but it is not working for me. Any suggestions?

My spreadsheet has four 'ranges' that I want to enter data into, say A1:D4, F1:I4, and A6:D9, F6:I9. IE four blocks of cells, contiguous within each block, but each block separated from the next by at least one row or column. When navigating with Tab and Enter, I want to stay within each block, and only be able to move to another block via the arrow keys or with the mouse. Can this be done?

Re: Excel formula for: if value is, then vlookup

Hi Krishnakumar.

Thank you. That worked perfectly, of course.

I want to do a vlookup (referring to a range on another sheet) in a cell, if the value in the preceding cell is "y".

I have tried =if(B2="y",=vlookup(B1,Clients,3,false),""), where

B2 is the cell on sheet 2 that I enter y or n
BI is the cell on Sheet 2 containing the 'trigger' value
Clients is the named range in Sheet 1
and 3 is the column on Sheet 1 to look up. But this doesn't work.

Any suggestions?

Thank you.

Re: If Then Else Not Returning Expected Result

I think the attachment didn't go last post because the file size was too big. I see that attachments must be only 46 kb or something. So I deleted half of my workbook, and half of the worksheet, but now the formula works. So there is no point in sending it now. I think I'll just file this under 'X-Files', and forget about it as quickly as possible. But thanks anyway. Unless you are curious enough about the problem for me to email you the problem workbook in its entirety (about 120kb).

Re: If Then Else Not Returning Expected Result

Quote from ByTheCringe2

Can you attach part of the workbook showing the error? I don't know what "autosum" is, but if there is any rounding involved, you may be getting the small floating-point arithmetic errors we keep hearing about. But that wouldn't work somewhere else in the workbook...

OK. I checked the help section of the site, and found how to attach a file. I think I have done it, but can't see it here. I'll post this anyway and see if it works. Else I'll try again.

Re: If Then Else Not Returning Expected Result

Can you explain to me how to attach a part of the workbook to a reply? I tried to do it with a simple copy and paste just now, but it doesn't show any formulas that I could see. Just text and data as if simly typed onto a page. I couldn't see how it would explain anything. Please excuse my lack of knowledge.

Re: If Then Else Not Returning Expected Result

I looked on some of your site's Excel tips and help guidelines, and tried to follow the advices re staying away from merged cells, etc. but no luck yet.

Re: If Then Else Not Returning Expected Result

All cells formatted as Accounting, 2 dec., symbol Au$. Font colours not the same, but I tried with colours all the same and it didn't help. Triple checked it, but still not working. I have copied and pasted again to another 'fresh' part of the worksheet, and it works fine. (Only trouble is that that is not where I want the cells to appear.) Quite a mystery to me.

I have the following formula in a cell, say A6, of a worksheet: =if(A5=A4,"Yes","NO !!!"). The data in A4 is the result of an autosum, and the data in A5 is a number value, simply typed in. Both A4 and A5 are formatted to Accounting, 2 decimal places. Even though the numerical result of the autosum in A4 is identical in value in A5, the value returned in A6 will only show "NO !!!". I have tried the formula in both another worksheet, and in another previously unused portion of the first worksheet, and it works fine. I have removed previously merged cell and other formats in the original cells, but to no avail. Any ideas (to save me having to redo the whole worksheet from scratch)?

Re: Applying Mask For Date Entries

OK. I've got it to work, but only in a fresh worksheet, with none on my other macros running or present. My limited knowledge is a definite handicap here. If one of you 'helpers' pick up this thread again, I'd still like to get it functioning in all my worksheets. Thanks.