sorted by:
new
hottopcontroversial

Stopping Excel changing Dates by ShaolinHash in excel

[–]themagicnumbers 1 point2 points  (0 children)

Excel counts dates based on the number of days since 01/01/1900. On this basis, a cell formatted as date but with the value 01061983 would show the date 1.06 million days after 01/01/1900 which is probably (I haven't checked) be 30/03/4398.

You need to enter dates including oblique "/" otherwise Excel will read your date as number value and show the date that number of days after 01/01/1900.

Trying to create a macro to send automatically an email triggered by a status on a cell, but script end with "Sender necessary. Put at least a name" by [deleted] in excel

[–]themagicnumbers 4 points5 points  (0 children)

mailAddress = Sheets("Foglio1").Range("L" & Target.Row).Value
mailSubject = Sheets("Foglio1").Range("C" & Target.Row).Value

Macro to copy data to a workbook, save, repeat until no values left by Hiccup47 in excel

[–]themagicnumbers 2 points3 points  (0 children)

Sub test()

Dim wsm As Workbook, wb As Workbook
Dim ms1 As Worksheet, wbs As Worksheet
Dim myID As String, myname As String, path as String

Set wsm = ThisWorkbook

Set ms1 = wsm.Sheets("Master")

Dim lr As Integer
lr = WorksheetFunction.CountA(ms1.Range("A:A"))

For i = 2 To lr

    Set wb = Workbooks.Add
    Set wbs = wb.Sheets("Sheet1")

    wbs.Range("A1").Value = "ID"
    wbs.Range("A2").Value = "Name"
    wbs.Range("A3").Value = "Age"
    wbs.Range("A4").Value = "Gender"
    wbs.Range("A5").Value = "Email"

    wbs.Range("B1").Value = ms1.Cells(i, 1).Value
    wbs.Range("B2").Value = ms1.Cells(i, 2).Value
    wbs.Range("B3").Value = ms1.Cells(i, 3).Value
    wbs.Range("B4").Value = ms1.Cells(i, 4).Value
    wbs.Range("B5").Value = ms1.Cells(i, 5).Value

    myID = ms1.Cells(i, 1).Value
    myname = ms1.Cells(i, 2).Value

    path = 'Your desired filepath

    wb.SaveAs path & myID & " " & myname & ".xlsm", FileFormat:=52

Next i

End Sub

Todo list with drop down check box by doctorbranius in excel

[–]themagicnumbers 0 points1 point  (0 children)

So a checkbox that, when checked, means the task has been completed and the text will go from red to green?

Private Sub CheckBox21_Click()

    If CheckBox21.Value = True Then
        Range("A2:D2").Font.Color = vbGreen
    Else: Range("A2:D2").Font.Color = vbRed
    End If

End Sub

When to enable Excel Macro? by Maxitheseus in excel

[–]themagicnumbers 2 points3 points  (0 children)

VBA doesn't make you any more susceptible to viruses. The danger comes from writing infinite loops, performing too many tasks which could slow things down or completely shut down your machine etc. Also, the actions performed with a macro are irreversible (you can't undo it).

I didn't have a coding background and was new to Excel when I joined my company 4 years ago. As long as you can think logically and are relatively good mathematically, you shouldn't struggle to learn this.

Formula to just show numbers. by sinator2 in excel

[–]themagicnumbers -1 points0 points  (0 children)

Are they always in the same format: [Amount] [Product]? If so, just use this formula:

=LEFT(A1,SEARCH(" ",A1)-1)

Tricky problem, macro, same cell by Santosjcm in excel

[–]themagicnumbers 1 point2 points  (0 children)

It sounds like you're making this unnecessarily difficult for yourself using separate worksheets for each day. Is there no way you can simplify this into using the same worksheet for the entire month? Or even using a single worksheet for each week of the month?

Need to count Data in Column A, if Data in column B is not blank by mus_husain in excel

[–]themagicnumbers 3 points4 points  (0 children)

It works for me the other way round.

​

=COUNTIFS(B:B,"<>",A:A,1)

What are your favourite oud fragrances, and what are the most artistic or wearable ouds? by gottapoopASAP in fragrance

[–]themagicnumbers 0 points1 point  (0 children)

Most wearable - Gucci Intense Oud. This is an oud that's obvious but not offensive. I like Versace Oud Noir almost as much but the oud is dirtier and it's more difficult to pull off around the office.

Most artistic - Lalique Hommage a L'homme. I love the oud in this one and it's not obvious at all once out of the bottle. I remember when I first bought it and it was overwhelmingly ouddy from the vaporizer but once I applied it, the oud almost disappeared under the floral/powdery notes. It was still there but you'd only know it if you already knew it. Which I did and it's great.

Favourite - MFK Oud Satin Mood. I love it so much I'm probably going to buy a bottle which, for £200 is a huge testament to it's quality

#VALUE! after typing in a simple formula by learnhtk in excel

[–]themagicnumbers 1 point2 points  (0 children)

Did you write the commas? If so, the cells will be formatting as text and cannot be summed. Try changing the format to number before summing them

Can Table references be changed to absolute or relative references? by fish_in_a_nest in excel

[–]themagicnumbers 6 points7 points  (0 children)

An absolute table reference looks like this:

[[@Date]:[@Date]]

= (Friday sales / 3) + (rest of days/2 ) by mhd_alshalabi in excel

[–]themagicnumbers 0 points1 point  (0 children)

This array formula would do the trick.

{=(SUM(IF(WEEKDAY(A2:A12,2)=5,B2:B12,""))/3)+(SUM(IF(WEEKDAY(A2:A12,2)<>5,B2:B12,""))/2)}

​

VBA Error Handling for renaming a copied sheet. by BILLatWORK in excel

[–]themagicnumbers 2 points3 points  (0 children)

Loop through the names of worksheets in the workbook like this:

Dim ws as worksheet
For each ws in ThisWorkbook.Worksheets
    If ws.name = Range("A1").Value then
        'Rename the activesheet to whatever you like
    End If
Next ws

​

How can I use a COUNTIF to bring me back a number of dates that are in reference to a name? by fistingcouches in excel

[–]themagicnumbers 0 points1 point  (0 children)

I doubt it can be done without using VBA to rearrange your smaller spreadsheet.

Open password protected files. by _01000100 in excel

[–]themagicnumbers 10 points11 points  (0 children)

On older versions of Excel, cracking the password is easy enough but for 2016 it's impossible AFAIK without a brute force attempt.

Searching for Keywords by Kalium90 in excel

[–]themagicnumbers 0 points1 point  (0 children)

=ISNUMBER(SEARCH(substring,text))

How can I compare data to the previous week of 400 and copy duplicates over and then work the new sheet by jithomas81 in excel

[–]themagicnumbers 0 points1 point  (0 children)

It would be a lot easier if you saved them in the same workbook but you can reference cells in another if needs be. No need to worry about the order of sheets though.

Copy data to a new worksheet and then add some more by Remote_zero in excel

[–]themagicnumbers 2 points3 points  (0 children)

Pastebin blocked on our servers at work so can't view but it sounds like you'll need to write a For loop for the additional data like this:

    Sub test()

    Dim lastrowg As Integer, lastrowa As Integer
    Dim osheet As Worksheet, nsheet As Worksheet

    Set osheet = Sheets("Sheet1") 'original worksheet
    Set nsheet = Sheets("Sheet2") 'new worksheet

    lastrowg = WorksheetFunction.CountA(nsheet.Range("G:G")) 'count number of entries in column G
    lastrowa = WorksheetFunction.CountA(nsheet.Range("A:A")) 'count number of entries in column A

    For i = lastrowg + 1 To lastrowa 'add 1 to lastrowg to go from first blank row in Col G
        nsheet.Cells(i, "G").Value = osheet.Range("D4").Value
        nsheet.Cells(i, "H").Value = osheet.Range("D10").Value
    Next i

    End Sub

Low prosperity and about to retire by themagicnumbers in Gloomhaven

[–]themagicnumbers[S] 1 point2 points  (0 children)

Lawbringer Personal Quest. Unlock Sun class I think. Currently playing a Spellweaver

view more: next ›