Hi people,

I am trying to move data from one sheet to a Invoice template, can someone give me a hand with a code to speed up data entry?

Highlighted in gray is the data that I want to copy and paste.

Note items in "source" may be up to 20.

Thanks,

I am trying to move data from one sheet to a Invoice template, can someone give me a hand with a code to speed up data entry?

Highlighted in gray is the data that I want to copy and paste.

Note items in "source" may be up to 20.

Thanks,

Hi all

I want to enter data into a "data" tab, with columns "date, name, description, amount" in A-D, but then have a summary tab that I will select a month and the lines will copy to the summary tab and be sorted by description and date, with a total for each description. Example attached.

Can it be done and, if so, how?

Thanks

Annie

I want to enter data into a "data" tab, with columns "date, name, description, amount" in A-D, but then have a summary tab that I will select a month and the lines will copy to the summary tab and be sorted by description and date, with a total for each description. Example attached.

Can it be done and, if so, how?

Thanks

Annie

Hi guys,

Complete VBA novice here! Just wondered if the following is possible.

I'm looking to delete every data point which falls outside 00:05:00 - 00:00:00 (Column B) across multiple sheets (all black coloured data gets deleted). Also if there are two equal times (i.e. 143342 01:03, 652143 01:03, the value with the highest figure in A gets deleted).

Thanks

Mdn

Complete VBA novice here! Just wondered if the following is possible.

I'm looking to delete every data point which falls outside 00:05:00 - 00:00:00 (Column B) across multiple sheets (all black coloured data gets deleted). Also if there are two equal times (i.e. 143342 01:03, 652143 01:03, the value with the highest figure in A gets deleted).

Thanks

Mdn

I need to make the fill color of one column match another, I have one column that lists equipment used in a user form sheet2! A1:A100. On my column on sheet 1 I have VBa set the fill color of column D of sheet 1 if it is broken turn red fixed turn green with an If then statement based off what “mode” my user form is in, report mode or fixed mode. Basically I want all my equipment listed on sheet 2 to stay fill green until it shows up on sheet 1 as fill red.

I’ve tried using conditional formatting but haven’t gotten anywhere with it and anything I try in VBA won’t run.

]]>I’ve tried using conditional formatting but haven’t gotten anywhere with it and anything I try in VBA won’t run.

Hello,

I would need your support regarding a dependent ComboBox.

I would like to see the unique values in ComboBox2 (<= called Application) based on the selection of ComboBox1 (<= called Product).

Please see the sample file with the details.

Thank you in advance for any feedback!

I would need your support regarding a dependent ComboBox.

I would like to see the unique values in ComboBox2 (<= called Application) based on the selection of ComboBox1 (<= called Product).

Please see the sample file with the details.

Thank you in advance for any feedback!

I need help copying specific cells from one worksheet to another. In the attached workbook, I wish to copy data in cells A4, C4:D4 and f4 on sheet1, onto Sheet2 into cells G7, G9:G10, G12

Thank Very Much

Thank Very Much

All the maximum formations up to 4 letters in each of the groups

ABCDE

F G H I J

* We have two groups

1st group = abcde

* 2nd group = fghij

Make all formations of a maximum of 4 letters

Abcd, abce, aabb .. aaaa aaab .....

Fghi fghj, ffgg, ffff fffg .....

After ready = cross Cartesian type

Abcd / fghi

]]>ABCDE

F G H I J

* We have two groups

1st group = abcde

* 2nd group = fghij

Make all formations of a maximum of 4 letters

Abcd, abce, aabb .. aaaa aaab .....

Fghi fghj, ffgg, ffff fffg .....

After ready = cross Cartesian type

Abcd / fghi

Dear Expert

Can you please modify the following code to do the following

1- only export range of data from Column A : column G

2- date in column D export it as text but in format DD,MM,YY

3-export all data in the same format

4- if can add line to show confirmation message that saved

]]>Can you please modify the following code to do the following

1- only export range of data from Column A : column G

2- date in column D export it as text but in format DD,MM,YY

3-export all data in the same format

4- if can add line to show confirmation message that saved

HTML Code:

`Private Sub CommandButton2_Click()`

Dim wb As Workbook, NewWb As Workbook, i As Long, wsname As String

Set wb = ActiveWorkbook

Application.ScreenUpdating = False

With ActiveSheet

wsname = .Name

With .Cells(1).CurrentRegion

.AutoFilter Field:=1, Criteria1:="*"

End With

.UsedRange.SpecialCells(xlCellTypeVisible).Copy

Set NewWb = Workbooks.Add

With ActiveSheet

.Range("A1").PasteSpecial xlPasteValues

.SaveAs Filename:=wb.Path & "\" & wsname & ".xlsx"

End With

NewWb.Close savechanges:=True

.AutoFilterMode = False

End With

Application.ScreenUpdating = True

End Sub

Hi.

Please be patient with the grammar :)

I'm using Excel 2013/2016

I have a spreadsheet with a list of contacts I would like to send email to.

On Sheet "Unike brukere" in column D (D2: D1500) is the email address of the people I want to send email to.

I have a simple macro that copies all the addresses and puts them in Outlook, the problem is that it does not work when the list is filtered. An email is created and all the e-mail addresses are copied. So the macro works perfectly when I want to send to everyone, but not when I only want to send to a small selection of contacts :)

Anyone who has suggestions for what's wrong?

This is my first post - hope it's OK to put code in this text.

Regards

Trond F.

===============================

]]>Please be patient with the grammar :)

I'm using Excel 2013/2016

I have a spreadsheet with a list of contacts I would like to send email to.

On Sheet "Unike brukere" in column D (D2: D1500) is the email address of the people I want to send email to.

I have a simple macro that copies all the addresses and puts them in Outlook, the problem is that it does not work when the list is filtered. An email is created and all the e-mail addresses are copied. So the macro works perfectly when I want to send to everyone, but not when I only want to send to a small selection of contacts :)

Anyone who has suggestions for what's wrong?

This is my first post - hope it's OK to put code in this text.

Regards

Trond F.

===============================

Code:

`Sub Send_ePost()`

Dim OutApp As Object

Dim OutMail As Object

Dim emailRng As Range, cl As Range

Dim sTo As String

Set emailRng = Worksheets("Unike brukere").Range("D2:D1500") 'e-mail addresses

For Each cl In emailRng

If Rows(cl.Row).EntireRow.Hidden = False Then 'Prevent lines that are filtered (hidden)

sTo = sTo & ";" & cl.Value 'Adding addresses with ; between

End If

Next

sTo = Mid(sTo, 2)

Set OutApp = CreateObject("Outlook.Application")

Set OutMail = OutApp.CreateItem(0)

On Error Resume Next

With OutMail

.to = sTo

.CC = ""

.BCC = ""

.Display

End With

On Error GoTo 0

Set OutMail = Nothing

Set OutApp = Nothing

End Sub

Hi,

I'm using the attached code to search through multiple sheets looking in column G for the word "open" then copy those rows to a sheet named "OpenIssues". Is there a way to add a second criteria to the search, looking for the month too. I have a date column and would like to pull the "open" issues for a specific month.

Thanks,

Madison

]]>I'm using the attached code to search through multiple sheets looking in column G for the word "open" then copy those rows to a sheet named "OpenIssues". Is there a way to add a second criteria to the search, looking for the month too. I have a date column and would like to pull the "open" issues for a specific month.

Thanks,

Madison

HTML Code:

Option Explicit

Option Compare Text '< ignore case

'

Sub SeachSheets()

'

Dim FirstAddress As String, WhatFor As String

Dim Cell As Range, Sheet As Worksheet

'

Rows("3:" & Rows.Count).ClearContents

WhatFor = InputBox("What are you looking for?", "Search Criteria", "Open")

If WhatFor = Empty Then Exit Sub

'

For Each Sheet In Sheets

If Sheet.Name <> "OpenIssues" Then

With Sheet.Columns(7)

Set Cell = .Find(WhatFor, LookIn:=xlValues, LookAt:=xlPart)

If Not Cell Is Nothing Then

FirstAddress = Cell.Address

Do

Cell.EntireRow.Copy _

Destination:=Sheets("OpenIssues").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

Set Cell = .FindNext(Cell)

Loop Until Cell Is Nothing Or Cell.Address = FirstAddress

End If

End With

End If

Next Sheet

'

Set Cell = Nothing

End Sub

I am currently using the following code to print a range of worksheets within my workbook. I was wondering however if I can add two printing stipulations to the code?

First, is it possible to add a line of code that will tell excel to print the sheets in color instead of black and white?

and secondly I was wondering how to add a line of code that will restrict printing to only those sheets that have active data on them. In other words excel should look to a cell on each page and if that cell has data then the page should be printed. if that cell is empty then excel should not print that worksheet and should move the the next one.

It would also be nice to be able to spectify a limit on the data to allow printing. if the percentage in the cell being looked at is 99% or lower then the page should be printed but if it is 100% or higher then the page should not print.

]]>Code:

`Sub printshiftweekly1()`

Dim rng As Range

Dim wks As Worksheet

For Each rng In Sheets("update page").Range("AB3:AB44")

If Trim(rng.Value) <> "" Then

On Error Resume Next

Set wks = Nothing

Set wks = Sheets(rng.Value)

On Error GoTo 0

If wks Is Nothing Then

MsgBox "Sheet " & rng.Value & " does not exist"

Else

wks.PrintOut

End If

End If

Next rng

End Sub

and secondly I was wondering how to add a line of code that will restrict printing to only those sheets that have active data on them. In other words excel should look to a cell on each page and if that cell has data then the page should be printed. if that cell is empty then excel should not print that worksheet and should move the the next one.

It would also be nice to be able to spectify a limit on the data to allow printing. if the percentage in the cell being looked at is 99% or lower then the page should be printed but if it is 100% or higher then the page should not print.

Hello, I'm just scratching the surface of VBA and I'm having trouble with a budget spreadsheet I'm working on.

Basically, I have my sheet protected so the user may only enter text into specific cells. But when empty, I would like those cells to have gray "example" text such as Paycheck, $0.00, etc.

I have 2 error messages that consistently pop up. Run-time error 1004 and Run-time error 13. When I UNprotect the sheet, Run-time error 1004 goes away. I get the Run-time error 13 whenever I double-click on a cell edited by a macro.

The formulas in the sheet work great, but my macro has errors and I don't know how to fix them. I'll post my macro here in case anyone can see where my problem is. Thank you in advance.

Option Explicit

'This checks for specific strings in cell values, and formats to gray text if found

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Cells.Value

Case "Paycheck"

Call FormatCell(Target)

Case "0"

Call FormatCell(Target)

Case "Consistent"

Call FormatCell(Target)

Case "Income"

Call FormatCell(Target)

Case "Remaining"

Call FormatCell(Target)

Case ""

'This line Defaults the 4 boxes at the top of the document to "Paycheck"

If Range("E5").Value = "" Then: Range("E5").Value = "Paycheck"

If Range("J5").Value = "" Then: Range("J5").Value = "Paycheck"

If Range("E8").Value = "" Then: Range("E8").Value = "Paycheck"

If Range("J8").Value = "" Then: Range("J8").Value = "Paycheck"

'This line Defaults the Paycheck amount to $0.00

If Range("H5").Value = "" Then: Range("H5").Value = "0"

If Range("M5").Value = "" Then: Range("M5").Value = "0"

If Range("H8").Value = "" Then: Range("H8").Value = "0"

If Range("M8").Value = "" Then: Range("M8").Value = "0"

'This line Defults the M14:M43 Columns to 0

If Range("M14").Value = "" Then: Range("M14").Value = "0"

If Range("M15").Value = "" Then: Range("M15").Value = "0"

If Range("M16").Value = "" Then: Range("M16").Value = "0"

If Range("M17").Value = "" Then: Range("M17").Value = "0"

If Range("M18").Value = "" Then: Range("M18").Value = "0"

If Range("M19").Value = "" Then: Range("M19").Value = "0"

If Range("M20").Value = "" Then: Range("M20").Value = "0"

If Range("M21").Value = "" Then: Range("M21").Value = "0"

If Range("M22").Value = "" Then: Range("M22").Value = "0"

If Range("M23").Value = "" Then: Range("M23").Value = "0"

If Range("M24").Value = "" Then: Range("M24").Value = "0"

If Range("M25").Value = "" Then: Range("M25").Value = "0"

If Range("M26").Value = "" Then: Range("M26").Value = "0"

If Range("M27").Value = "" Then: Range("M27").Value = "0"

If Range("M28").Value = "" Then: Range("M28").Value = "0"

If Range("M29").Value = "" Then: Range("M29").Value = "0"

If Range("M30").Value = "" Then: Range("M30").Value = "0"

If Range("M31").Value = "" Then: Range("M31").Value = "0"

If Range("M32").Value = "" Then: Range("M32").Value = "0"

If Range("M33").Value = "" Then: Range("M33").Value = "0"

If Range("M34").Value = "" Then: Range("M34").Value = "0"

If Range("M35").Value = "" Then: Range("M35").Value = "0"

If Range("M36").Value = "" Then: Range("M36").Value = "0"

If Range("M37").Value = "" Then: Range("M37").Value = "0"

If Range("M38").Value = "" Then: Range("M38").Value = "0"

If Range("M39").Value = "" Then: Range("M39").Value = "0"

If Range("M40").Value = "" Then: Range("M40").Value = "0"

If Range("M41").Value = "" Then: Range("M41").Value = "0"

If Range("M42").Value = "" Then: Range("M42").Value = "0"

If Range("M43").Value = "" Then: Range("M43").Value = "0"

'This line Defults the lower 2 P50:P69 & P78:P97 Columns to 0

If Range("P50").Value = "" Then: Range("P50").Value = "0"

If Range("P51").Value = "" Then: Range("P51").Value = "0"

If Range("P52").Value = "" Then: Range("P52").Value = "0"

If Range("P53").Value = "" Then: Range("P53").Value = "0"

If Range("P54").Value = "" Then: Range("P54").Value = "0"

If Range("P55").Value = "" Then: Range("P55").Value = "0"

If Range("P56").Value = "" Then: Range("P56").Value = "0"

If Range("P57").Value = "" Then: Range("P57").Value = "0"

If Range("P58").Value = "" Then: Range("P58").Value = "0"

If Range("P59").Value = "" Then: Range("P59").Value = "0"

If Range("P60").Value = "" Then: Range("P60").Value = "0"

If Range("P61").Value = "" Then: Range("P61").Value = "0"

If Range("P62").Value = "" Then: Range("P62").Value = "0"

If Range("P63").Value = "" Then: Range("P63").Value = "0"

If Range("P64").Value = "" Then: Range("P64").Value = "0"

If Range("P65").Value = "" Then: Range("P65").Value = "0"

If Range("P66").Value = "" Then: Range("P66").Value = "0"

If Range("P67").Value = "" Then: Range("P67").Value = "0"

If Range("P68").Value = "" Then: Range("P68").Value = "0"

If Range("P69").Value = "" Then: Range("P69").Value = "0"

If Range("P78").Value = "" Then: Range("P78").Value = "0"

If Range("P79").Value = "" Then: Range("P79").Value = "0"

If Range("P80").Value = "" Then: Range("P80").Value = "0"

If Range("P81").Value = "" Then: Range("P81").Value = "0"

If Range("P82").Value = "" Then: Range("P82").Value = "0"

If Range("P83").Value = "" Then: Range("P83").Value = "0"

If Range("P84").Value = "" Then: Range("P84").Value = "0"

If Range("P85").Value = "" Then: Range("P85").Value = "0"

If Range("P86").Value = "" Then: Range("P86").Value = "0"

If Range("P87").Value = "" Then: Range("P87").Value = "0"

If Range("P88").Value = "" Then: Range("P88").Value = "0"

If Range("P89").Value = "" Then: Range("P89").Value = "0"

If Range("P90").Value = "" Then: Range("P90").Value = "0"

If Range("P91").Value = "" Then: Range("P91").Value = "0"

If Range("P92").Value = "" Then: Range("P92").Value = "0"

If Range("P93").Value = "" Then: Range("P93").Value = "0"

If Range("P94").Value = "" Then: Range("P94").Value = "0"

If Range("P95").Value = "" Then: Range("P95").Value = "0"

If Range("P96").Value = "" Then: Range("P96").Value = "0"

If Range("P97").Value = "" Then: Range("P97").Value = "0"

'This line defaults the Consistent section to say "Consistent"

If Range("G14").Value = "" Then: Range("G14").Value = "Consistent"

If Range("G15").Value = "" Then: Range("G15").Value = "Consistent"

If Range("G16").Value = "" Then: Range("G16").Value = "Consistent"

If Range("G17").Value = "" Then: Range("G17").Value = "Consistent"

If Range("G18").Value = "" Then: Range("G18").Value = "Consistent"

If Range("G19").Value = "" Then: Range("G19").Value = "Consistent"

If Range("G20").Value = "" Then: Range("G20").Value = "Consistent"

If Range("G21").Value = "" Then: Range("G21").Value = "Consistent"

If Range("G22").Value = "" Then: Range("G22").Value = "Consistent"

If Range("G23").Value = "" Then: Range("G23").Value = "Consistent"

If Range("G24").Value = "" Then: Range("G24").Value = "Consistent"

If Range("G25").Value = "" Then: Range("G25").Value = "Consistent"

If Range("G26").Value = "" Then: Range("G26").Value = "Consistent"

If Range("G27").Value = "" Then: Range("G27").Value = "Consistent"

If Range("G28").Value = "" Then: Range("G28").Value = "Consistent"

If Range("G29").Value = "" Then: Range("G29").Value = "Consistent"

If Range("G30").Value = "" Then: Range("G30").Value = "Consistent"

If Range("G31").Value = "" Then: Range("G31").Value = "Consistent"

If Range("G32").Value = "" Then: Range("G32").Value = "Consistent"

If Range("G33").Value = "" Then: Range("G33").Value = "Consistent"

If Range("G34").Value = "" Then: Range("G34").Value = "Consistent"

If Range("G35").Value = "" Then: Range("G35").Value = "Consistent"

If Range("G36").Value = "" Then: Range("G36").Value = "Consistent"

If Range("G37").Value = "" Then: Range("G37").Value = "Consistent"

If Range("G38").Value = "" Then: Range("G38").Value = "Consistent"

If Range("G39").Value = "" Then: Range("G39").Value = "Consistent"

If Range("G40").Value = "" Then: Range("G40").Value = "Consistent"

If Range("G41").Value = "" Then: Range("G41").Value = "Consistent"

If Range("G42").Value = "" Then: Range("G42").Value = "Consistent"

If Range("G43").Value = "" Then: Range("G43").Value = "Consistent"

'This line defaults the Percentage of Income section to say "Income"

If Range("G50").Value = "" Then: Range("G50").Value = "Income"

If Range("G51").Value = "" Then: Range("G51").Value = "Income"

If Range("G52").Value = "" Then: Range("G52").Value = "Income"

If Range("G53").Value = "" Then: Range("G53").Value = "Income"

If Range("G54").Value = "" Then: Range("G54").Value = "Income"

If Range("G55").Value = "" Then: Range("G55").Value = "Income"

If Range("G56").Value = "" Then: Range("G56").Value = "Income"

If Range("G57").Value = "" Then: Range("G57").Value = "Income"

If Range("G58").Value = "" Then: Range("G58").Value = "Income"

If Range("G59").Value = "" Then: Range("G59").Value = "Income"

If Range("G60").Value = "" Then: Range("G60").Value = "Income"

If Range("G61").Value = "" Then: Range("G61").Value = "Income"

If Range("G62").Value = "" Then: Range("G62").Value = "Income"

If Range("G63").Value = "" Then: Range("G63").Value = "Income"

If Range("G64").Value = "" Then: Range("G64").Value = "Income"

If Range("G65").Value = "" Then: Range("G65").Value = "Income"

If Range("G66").Value = "" Then: Range("G66").Value = "Income"

If Range("G67").Value = "" Then: Range("G67").Value = "Income"

If Range("G68").Value = "" Then: Range("G68").Value = "Income"

If Range("G69").Value = "" Then: Range("G69").Value = "Income"

'This line defaults the Percentage of Remaining section to say "Remaining"

If Range("G78").Value = "" Then: Range("G78").Value = "Remaining"

If Range("G79").Value = "" Then: Range("G79").Value = "Remaining"

If Range("G80").Value = "" Then: Range("G80").Value = "Remaining"

If Range("G81").Value = "" Then: Range("G81").Value = "Remaining"

If Range("G82").Value = "" Then: Range("G82").Value = "Remaining"

If Range("G83").Value = "" Then: Range("G83").Value = "Remaining"

If Range("G84").Value = "" Then: Range("G84").Value = "Remaining"

If Range("G85").Value = "" Then: Range("G85").Value = "Remaining"

If Range("G86").Value = "" Then: Range("G86").Value = "Remaining"

If Range("G87").Value = "" Then: Range("G87").Value = "Remaining"

If Range("G88").Value = "" Then: Range("G88").Value = "Remaining"

If Range("G89").Value = "" Then: Range("G89").Value = "Remaining"

If Range("G90").Value = "" Then: Range("G90").Value = "Remaining"

If Range("G91").Value = "" Then: Range("G91").Value = "Remaining"

If Range("G92").Value = "" Then: Range("G92").Value = "Remaining"

If Range("G93").Value = "" Then: Range("G93").Value = "Remaining"

If Range("G94").Value = "" Then: Range("G94").Value = "Remaining"

If Range("G95").Value = "" Then: Range("G95").Value = "Remaining"

If Range("G96").Value = "" Then: Range("G96").Value = "Remaining"

If Range("G97").Value = "" Then: Range("G97").Value = "Remaining"

Case Else

Target.Cells.Font.Color = &H0

End Select

End Sub

'This clears a cell's contents when someone double clicks the cells to edit its value

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Target.Cells.Value = ""

End Sub

'This formats the font color back to black so user data is easily readable

Private Sub FormatCell(ByVal Target As Range)

Target.Cells.Font.Color = &H808080

End Sub

Sub macroProtect3()

Sheet2.Protect Password:="0000", UserInterFaceOnly:=True

'enter code

Sheet2.Cells(1, 1) = UCase("")

End Sub

]]>Basically, I have my sheet protected so the user may only enter text into specific cells. But when empty, I would like those cells to have gray "example" text such as Paycheck, $0.00, etc.

I have 2 error messages that consistently pop up. Run-time error 1004 and Run-time error 13. When I UNprotect the sheet, Run-time error 1004 goes away. I get the Run-time error 13 whenever I double-click on a cell edited by a macro.

The formulas in the sheet work great, but my macro has errors and I don't know how to fix them. I'll post my macro here in case anyone can see where my problem is. Thank you in advance.

Option Explicit

'This checks for specific strings in cell values, and formats to gray text if found

Private Sub Worksheet_Change(ByVal Target As Range)

Select Case Target.Cells.Value

Case "Paycheck"

Call FormatCell(Target)

Case "0"

Call FormatCell(Target)

Case "Consistent"

Call FormatCell(Target)

Case "Income"

Call FormatCell(Target)

Case "Remaining"

Call FormatCell(Target)

Case ""

'This line Defaults the 4 boxes at the top of the document to "Paycheck"

If Range("E5").Value = "" Then: Range("E5").Value = "Paycheck"

If Range("J5").Value = "" Then: Range("J5").Value = "Paycheck"

If Range("E8").Value = "" Then: Range("E8").Value = "Paycheck"

If Range("J8").Value = "" Then: Range("J8").Value = "Paycheck"

'This line Defaults the Paycheck amount to $0.00

If Range("H5").Value = "" Then: Range("H5").Value = "0"

If Range("M5").Value = "" Then: Range("M5").Value = "0"

If Range("H8").Value = "" Then: Range("H8").Value = "0"

If Range("M8").Value = "" Then: Range("M8").Value = "0"

'This line Defults the M14:M43 Columns to 0

If Range("M14").Value = "" Then: Range("M14").Value = "0"

If Range("M15").Value = "" Then: Range("M15").Value = "0"

If Range("M16").Value = "" Then: Range("M16").Value = "0"

If Range("M17").Value = "" Then: Range("M17").Value = "0"

If Range("M18").Value = "" Then: Range("M18").Value = "0"

If Range("M19").Value = "" Then: Range("M19").Value = "0"

If Range("M20").Value = "" Then: Range("M20").Value = "0"

If Range("M21").Value = "" Then: Range("M21").Value = "0"

If Range("M22").Value = "" Then: Range("M22").Value = "0"

If Range("M23").Value = "" Then: Range("M23").Value = "0"

If Range("M24").Value = "" Then: Range("M24").Value = "0"

If Range("M25").Value = "" Then: Range("M25").Value = "0"

If Range("M26").Value = "" Then: Range("M26").Value = "0"

If Range("M27").Value = "" Then: Range("M27").Value = "0"

If Range("M28").Value = "" Then: Range("M28").Value = "0"

If Range("M29").Value = "" Then: Range("M29").Value = "0"

If Range("M30").Value = "" Then: Range("M30").Value = "0"

If Range("M31").Value = "" Then: Range("M31").Value = "0"

If Range("M32").Value = "" Then: Range("M32").Value = "0"

If Range("M33").Value = "" Then: Range("M33").Value = "0"

If Range("M34").Value = "" Then: Range("M34").Value = "0"

If Range("M35").Value = "" Then: Range("M35").Value = "0"

If Range("M36").Value = "" Then: Range("M36").Value = "0"

If Range("M37").Value = "" Then: Range("M37").Value = "0"

If Range("M38").Value = "" Then: Range("M38").Value = "0"

If Range("M39").Value = "" Then: Range("M39").Value = "0"

If Range("M40").Value = "" Then: Range("M40").Value = "0"

If Range("M41").Value = "" Then: Range("M41").Value = "0"

If Range("M42").Value = "" Then: Range("M42").Value = "0"

If Range("M43").Value = "" Then: Range("M43").Value = "0"

'This line Defults the lower 2 P50:P69 & P78:P97 Columns to 0

If Range("P50").Value = "" Then: Range("P50").Value = "0"

If Range("P51").Value = "" Then: Range("P51").Value = "0"

If Range("P52").Value = "" Then: Range("P52").Value = "0"

If Range("P53").Value = "" Then: Range("P53").Value = "0"

If Range("P54").Value = "" Then: Range("P54").Value = "0"

If Range("P55").Value = "" Then: Range("P55").Value = "0"

If Range("P56").Value = "" Then: Range("P56").Value = "0"

If Range("P57").Value = "" Then: Range("P57").Value = "0"

If Range("P58").Value = "" Then: Range("P58").Value = "0"

If Range("P59").Value = "" Then: Range("P59").Value = "0"

If Range("P60").Value = "" Then: Range("P60").Value = "0"

If Range("P61").Value = "" Then: Range("P61").Value = "0"

If Range("P62").Value = "" Then: Range("P62").Value = "0"

If Range("P63").Value = "" Then: Range("P63").Value = "0"

If Range("P64").Value = "" Then: Range("P64").Value = "0"

If Range("P65").Value = "" Then: Range("P65").Value = "0"

If Range("P66").Value = "" Then: Range("P66").Value = "0"

If Range("P67").Value = "" Then: Range("P67").Value = "0"

If Range("P68").Value = "" Then: Range("P68").Value = "0"

If Range("P69").Value = "" Then: Range("P69").Value = "0"

If Range("P78").Value = "" Then: Range("P78").Value = "0"

If Range("P79").Value = "" Then: Range("P79").Value = "0"

If Range("P80").Value = "" Then: Range("P80").Value = "0"

If Range("P81").Value = "" Then: Range("P81").Value = "0"

If Range("P82").Value = "" Then: Range("P82").Value = "0"

If Range("P83").Value = "" Then: Range("P83").Value = "0"

If Range("P84").Value = "" Then: Range("P84").Value = "0"

If Range("P85").Value = "" Then: Range("P85").Value = "0"

If Range("P86").Value = "" Then: Range("P86").Value = "0"

If Range("P87").Value = "" Then: Range("P87").Value = "0"

If Range("P88").Value = "" Then: Range("P88").Value = "0"

If Range("P89").Value = "" Then: Range("P89").Value = "0"

If Range("P90").Value = "" Then: Range("P90").Value = "0"

If Range("P91").Value = "" Then: Range("P91").Value = "0"

If Range("P92").Value = "" Then: Range("P92").Value = "0"

If Range("P93").Value = "" Then: Range("P93").Value = "0"

If Range("P94").Value = "" Then: Range("P94").Value = "0"

If Range("P95").Value = "" Then: Range("P95").Value = "0"

If Range("P96").Value = "" Then: Range("P96").Value = "0"

If Range("P97").Value = "" Then: Range("P97").Value = "0"

'This line defaults the Consistent section to say "Consistent"

If Range("G14").Value = "" Then: Range("G14").Value = "Consistent"

If Range("G15").Value = "" Then: Range("G15").Value = "Consistent"

If Range("G16").Value = "" Then: Range("G16").Value = "Consistent"

If Range("G17").Value = "" Then: Range("G17").Value = "Consistent"

If Range("G18").Value = "" Then: Range("G18").Value = "Consistent"

If Range("G19").Value = "" Then: Range("G19").Value = "Consistent"

If Range("G20").Value = "" Then: Range("G20").Value = "Consistent"

If Range("G21").Value = "" Then: Range("G21").Value = "Consistent"

If Range("G22").Value = "" Then: Range("G22").Value = "Consistent"

If Range("G23").Value = "" Then: Range("G23").Value = "Consistent"

If Range("G24").Value = "" Then: Range("G24").Value = "Consistent"

If Range("G25").Value = "" Then: Range("G25").Value = "Consistent"

If Range("G26").Value = "" Then: Range("G26").Value = "Consistent"

If Range("G27").Value = "" Then: Range("G27").Value = "Consistent"

If Range("G28").Value = "" Then: Range("G28").Value = "Consistent"

If Range("G29").Value = "" Then: Range("G29").Value = "Consistent"

If Range("G30").Value = "" Then: Range("G30").Value = "Consistent"

If Range("G31").Value = "" Then: Range("G31").Value = "Consistent"

If Range("G32").Value = "" Then: Range("G32").Value = "Consistent"

If Range("G33").Value = "" Then: Range("G33").Value = "Consistent"

If Range("G34").Value = "" Then: Range("G34").Value = "Consistent"

If Range("G35").Value = "" Then: Range("G35").Value = "Consistent"

If Range("G36").Value = "" Then: Range("G36").Value = "Consistent"

If Range("G37").Value = "" Then: Range("G37").Value = "Consistent"

If Range("G38").Value = "" Then: Range("G38").Value = "Consistent"

If Range("G39").Value = "" Then: Range("G39").Value = "Consistent"

If Range("G40").Value = "" Then: Range("G40").Value = "Consistent"

If Range("G41").Value = "" Then: Range("G41").Value = "Consistent"

If Range("G42").Value = "" Then: Range("G42").Value = "Consistent"

If Range("G43").Value = "" Then: Range("G43").Value = "Consistent"

'This line defaults the Percentage of Income section to say "Income"

If Range("G50").Value = "" Then: Range("G50").Value = "Income"

If Range("G51").Value = "" Then: Range("G51").Value = "Income"

If Range("G52").Value = "" Then: Range("G52").Value = "Income"

If Range("G53").Value = "" Then: Range("G53").Value = "Income"

If Range("G54").Value = "" Then: Range("G54").Value = "Income"

If Range("G55").Value = "" Then: Range("G55").Value = "Income"

If Range("G56").Value = "" Then: Range("G56").Value = "Income"

If Range("G57").Value = "" Then: Range("G57").Value = "Income"

If Range("G58").Value = "" Then: Range("G58").Value = "Income"

If Range("G59").Value = "" Then: Range("G59").Value = "Income"

If Range("G60").Value = "" Then: Range("G60").Value = "Income"

If Range("G61").Value = "" Then: Range("G61").Value = "Income"

If Range("G62").Value = "" Then: Range("G62").Value = "Income"

If Range("G63").Value = "" Then: Range("G63").Value = "Income"

If Range("G64").Value = "" Then: Range("G64").Value = "Income"

If Range("G65").Value = "" Then: Range("G65").Value = "Income"

If Range("G66").Value = "" Then: Range("G66").Value = "Income"

If Range("G67").Value = "" Then: Range("G67").Value = "Income"

If Range("G68").Value = "" Then: Range("G68").Value = "Income"

If Range("G69").Value = "" Then: Range("G69").Value = "Income"

'This line defaults the Percentage of Remaining section to say "Remaining"

If Range("G78").Value = "" Then: Range("G78").Value = "Remaining"

If Range("G79").Value = "" Then: Range("G79").Value = "Remaining"

If Range("G80").Value = "" Then: Range("G80").Value = "Remaining"

If Range("G81").Value = "" Then: Range("G81").Value = "Remaining"

If Range("G82").Value = "" Then: Range("G82").Value = "Remaining"

If Range("G83").Value = "" Then: Range("G83").Value = "Remaining"

If Range("G84").Value = "" Then: Range("G84").Value = "Remaining"

If Range("G85").Value = "" Then: Range("G85").Value = "Remaining"

If Range("G86").Value = "" Then: Range("G86").Value = "Remaining"

If Range("G87").Value = "" Then: Range("G87").Value = "Remaining"

If Range("G88").Value = "" Then: Range("G88").Value = "Remaining"

If Range("G89").Value = "" Then: Range("G89").Value = "Remaining"

If Range("G90").Value = "" Then: Range("G90").Value = "Remaining"

If Range("G91").Value = "" Then: Range("G91").Value = "Remaining"

If Range("G92").Value = "" Then: Range("G92").Value = "Remaining"

If Range("G93").Value = "" Then: Range("G93").Value = "Remaining"

If Range("G94").Value = "" Then: Range("G94").Value = "Remaining"

If Range("G95").Value = "" Then: Range("G95").Value = "Remaining"

If Range("G96").Value = "" Then: Range("G96").Value = "Remaining"

If Range("G97").Value = "" Then: Range("G97").Value = "Remaining"

Case Else

Target.Cells.Font.Color = &H0

End Select

End Sub

'This clears a cell's contents when someone double clicks the cells to edit its value

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Target.Cells.Value = ""

End Sub

'This formats the font color back to black so user data is easily readable

Private Sub FormatCell(ByVal Target As Range)

Target.Cells.Font.Color = &H808080

End Sub

Sub macroProtect3()

Sheet2.Protect Password:="0000", UserInterFaceOnly:=True

'enter code

Sheet2.Cells(1, 1) = UCase("")

End Sub

Hi

Is there anyone who knows a macro event that can be activated

1. if a shape is selected (to show extra information about the meaning of that shape) BUT ALSO...

2. if the user changes the sieze of that shape AND (to change the information based on it's new size)

3. if the user moved that same shape (to change the information based on it's new position)

I can not add the macro to the shape itself, because then you can not change the format of the shape or move the shape (without right clicking on it).

Selectionchange or worksheetchange does not work for this.

Thanks a lot!!!

]]>Is there anyone who knows a macro event that can be activated

1. if a shape is selected (to show extra information about the meaning of that shape) BUT ALSO...

2. if the user changes the sieze of that shape AND (to change the information based on it's new size)

3. if the user moved that same shape (to change the information based on it's new position)

I can not add the macro to the shape itself, because then you can not change the format of the shape or move the shape (without right clicking on it).

Selectionchange or worksheetchange does not work for this.

Thanks a lot!!!

Dear Sir/Madam,

I have a data set which contains information between buyers and sellers for a certain period of time. I have multiple buyers, multiple sellers and multiple times. For each buyer, there are situations where the given buyer has more than one deals with the same seller at a given time.

I need to generate a weighted average of the price for a given pair of trading relationship at a given moment of time (e.g year). The weight I am using is the amount of trade for each deal between the same pair at a given moment of time divided the total amount of all deals for the pair at the same moment of time.

for example, buyer A and seller B have 3 deals in day one. The first deal is 20 apples and the price is 1 dollar each apple. the second deal is 30 apples and the price is 1.1 dollar each apple and the third deal is 25 apples and the price is 0.9 each apple. I am intended to calculate the weighted average price of apple for A and B in day one as follows: (20/(20+30+25))*1+(30/(20+30+25))*1.1+(25/(20+30+25))*0.9

I understand I can use excel comment such as sumif/sumifs together with others to achieve what I want. But my case in question is more complicated since I have quite large number of buyers, quite large number of sellers, quite large number of times and high proportion of deals between the same pair for a given moment of time.

I hope to get your help by using VBA.

I enclose a hypothetical example of my data set here.

Thank you for your help and I hope to hear from you soon.

Tianshu

I have a data set which contains information between buyers and sellers for a certain period of time. I have multiple buyers, multiple sellers and multiple times. For each buyer, there are situations where the given buyer has more than one deals with the same seller at a given time.

I need to generate a weighted average of the price for a given pair of trading relationship at a given moment of time (e.g year). The weight I am using is the amount of trade for each deal between the same pair at a given moment of time divided the total amount of all deals for the pair at the same moment of time.

for example, buyer A and seller B have 3 deals in day one. The first deal is 20 apples and the price is 1 dollar each apple. the second deal is 30 apples and the price is 1.1 dollar each apple and the third deal is 25 apples and the price is 0.9 each apple. I am intended to calculate the weighted average price of apple for A and B in day one as follows: (20/(20+30+25))*1+(30/(20+30+25))*1.1+(25/(20+30+25))*0.9

I understand I can use excel comment such as sumif/sumifs together with others to achieve what I want. But my case in question is more complicated since I have quite large number of buyers, quite large number of sellers, quite large number of times and high proportion of deals between the same pair for a given moment of time.

I hope to get your help by using VBA.

I enclose a hypothetical example of my data set here.

Thank you for your help and I hope to hear from you soon.

Tianshu

In the attachment, I have a pivot table with macro, when i run, it creates three files i.e West, South & North, at

c:\izhar, but the problem is, there is no data in these three files, i need help in this regard.

c:\izhar, but the problem is, there is no data in these three files, i need help in this regard.