I'm getting error 13 on this line, but I don't know why. do you know what to change.

]]>PHP Code:

```
If IsEmpty(Range("G:G")) Or Range("G:G").Value = 0 And Range("H:H").Value = False Then
```

Good morning,

I hope you are all well.

I am having extremely bad luck with my coding just lately, I am very much a beginner and a copy & paste expert (I'm not afraid to admit it).

I have hit a brick wall though.

Basically the company I work for needs me to create a portal in excel where managers can save files of their employees to their 'employee file'.

So I have made a sheet where they can select which file they want to save, which then generates a path in which the file will be saved to.

The issue I have is that I need the path to generate the associated folders (which will be their name), so then I do not need to add a new folder each time a new employee starts, this folder will automatically be created in the destination folder when they submit the file, which is then saved in the newly created folder.

Could anybody help me with this?

Any help you could provide would be greatly appreciated.

2023-03-29 11_17_13-Copy file from source path in cell, to destination path in cell, and create .jpg

I hope you are all well.

I am having extremely bad luck with my coding just lately, I am very much a beginner and a copy & paste expert (I'm not afraid to admit it).

I have hit a brick wall though.

Basically the company I work for needs me to create a portal in excel where managers can save files of their employees to their 'employee file'.

So I have made a sheet where they can select which file they want to save, which then generates a path in which the file will be saved to.

The issue I have is that I need the path to generate the associated folders (which will be their name), so then I do not need to add a new folder each time a new employee starts, this folder will automatically be created in the destination folder when they submit the file, which is then saved in the newly created folder.

Could anybody help me with this?

Any help you could provide would be greatly appreciated.

2023-03-29 11_17_13-Copy file from source path in cell, to destination path in cell, and create .jpg

Hi All,

Wonder if you can help.

I've attached a sample worksheet showing roughly what I'm trying to achieve - I'm completely stumped.

Column B just shows an item code.

Column C shows the quantity

Column D is the rate for the item

Column E is where the formula that I need help with is.

If (In column E) it looks up the table and there is a whole number then just multiply rate x quantity. BUT, if the rate is a percentage then multiply by the percent.

I can only get it to multiply the whole number and not differentiate.

I've added a column (F) to show the answer that it should be.

Thanks all

(P.S. I'll probably be posting this in Mr Excel too as I'm really hoping to have an answer before my team meeting tomorrow - Thanks again)

Hoopsah

Wonder if you can help.

I've attached a sample worksheet showing roughly what I'm trying to achieve - I'm completely stumped.

Column B just shows an item code.

Column C shows the quantity

Column D is the rate for the item

Column E is where the formula that I need help with is.

If (In column E) it looks up the table and there is a whole number then just multiply rate x quantity. BUT, if the rate is a percentage then multiply by the percent.

I can only get it to multiply the whole number and not differentiate.

I've added a column (F) to show the answer that it should be.

Thanks all

(P.S. I'll probably be posting this in Mr Excel too as I'm really hoping to have an answer before my team meeting tomorrow - Thanks again)

Hoopsah

Hi, everyone,

I attached a file, in hope that you will understand my need. I tried using Vlookup, but as you know, vlookup only finds first value. And my Excel knowledge is very limited.

Basically, I need a formula to take the information from Database sheet (source) and place them in the CAR sheet, matching the date and the CAR name. I need this because i have a bigger number of cars to which i need to create a Daily activity sheet for vehicles.And if I could automatically import the information it would save me tons on time.

Hope you will take some time to check the attachement. Many Thanks !

I attached a file, in hope that you will understand my need. I tried using Vlookup, but as you know, vlookup only finds first value. And my Excel knowledge is very limited.

Basically, I need a formula to take the information from Database sheet (source) and place them in the CAR sheet, matching the date and the CAR name. I need this because i have a bigger number of cars to which i need to create a Daily activity sheet for vehicles.And if I could automatically import the information it would save me tons on time.

Hope you will take some time to check the attachement. Many Thanks !

Select the source folder, and search for matches with the excel 1 file.

The files that exist in excel 1 will be searched in the source folder and copied to the destination folder.

I need help about one menu for selecting the source folder, because which one doesnt give the value to the main code....

The files that exist in excel 1 will be searched in the source folder and copied to the destination folder.

I need help about one menu for selecting the source folder, because which one doesnt give the value to the main code....

Dear All,

I am a newbie at VBA and I would like to create a function (not a Sub) to be used at any range and for any given value in the workbook. The Function should be used for replacing characters in the values. So far I have tried 2 codings:

1st:

2nd:

For both above functions I get the result: #VALUE!

Thank you for your help!

]]>I am a newbie at VBA and I would like to create a function (not a Sub) to be used at any range and for any given value in the workbook. The Function should be used for replacing characters in the values. So far I have tried 2 codings:

1st:

Code:

`Function Chatrim(incorref As Double) As Double`

Chatrim = replace(incorref, "2022/", "") incorref is the chosen value and the expected result would be like : incorref = 2022/445 expected result with the function is 445

End Function

Code:

`Function Replacechar(incorvalue As Double) As Double`

Range(incorvalue).replace What:="2022/", Replacement:=""

'incorvalue is the chosen value and the expected result would be like : incorvalue = 2022/445 expected result with the function is 445

End Function

Thank you for your help!

Just recently I came across the following statement

Suppose we are discussing latitude and longitude in WGS84 and we want to be accurate. Both meridian radius of curvature M (latitude length) and radius of curvature of parallels R (longitude length) in WGS84 depend only on latitude phi and are defined as:

M = a(1 - e^2) / (1 - e^2 sin(phi)^2)^(3/2)

R = a cos(phi) / (1 - e^2 sin(phi)^2)^(1/2)

where

e is the eccentricity of the referenced ellipsoid of WGS84

a is the semi-major axis of the ellipsoid.

WGS 84 defines the semi-major axis of the WGS 84 ellipsoid a and the flattening factor of the Earth f as:

a=6378137m

1/f=298.257223563

With these defining parameters in WGS 84, we can obtain the eccentricity:

e=sqrt(2f-f^2)=0.081819191

Verification of the above equations

To verify whether the above eq. (1) and eq. (2) are correct, we will check them against the results at USGS which said, at 38 degrees North latitude, one degree of latitude equals approximately 364,000 feet (69 miles) and one degree of longitude equals 288,200 feet (54.6 miles).

To find the meridian radius of curvature M (latitude length) and radius of curvature of parallels (longitude length) at 38 degrees North latitude, we plug in a=6378137, e=0.081819191 and phi=38° into eq. (1) and eq. (2):

M = 6359629.652 m/rad

R = 5032429.322 m/rad

Furthermore, to find length of 1° of latitude and longitude, we multiply the above equations by the radian of 1°:

Length of 1° latitude = M * 1°/180° * pi = 6359629.652*1*pi/180 = 110996.4766 m = 68.970013 miles

Length of 1° longitude= R * 1°/180° * pi = 5032429.322*1*pi/180 = 87832.46103 m = 54.57656101 miles

The results are in line with the results of USGS.

Answer to your question

Repeat the above steps but plug in phi=37°15.8298′ = 37+15.8298/60=37.26383, we can obtain:

Length of 1° latitude=68.96139 miles

Length of 1° longitude=55.11761 miles

I am trying firstly understand the equations and secondly evaluate them for accuracy. In using Office 365 for Mac, Excel refuses to recognise the formulas for M & R. Could this be because the formulas might be missing a Math Operator? Simply replacing "PHI" with the Latitude (in Decimal Degrees) still results in an error message " are you trying to enter a formula?". Next I run into an error when I tried to check the validity of the value of "e". The formula as written in the statement above returns a "Num error because we haven't define the value of "f". When I replaced "f" in the equation to determine "e", it returned the #Num error message. What am I doing wrong?

If I understand the 1/f =298.257223563 statement correctly, by transposing f= 298.257223563*1, or is this assumption incorrect?

Eventually I'd like to test the formulas to determine the lengths of 1° of Latitude and or Longitude. Any assistance would be greatly appreciated. On a side note. Supporters of Office 365 often say that the improvements under 365, significantly reduce the need to resort to VBA. Given that we are dealing with values with 9 decimals places can this overcome the limitation of floating point error, or is Office 365 still a fair way off this accuracy required?

]]>Quote:

Suppose we are discussing latitude and longitude in WGS84 and we want to be accurate. Both meridian radius of curvature M (latitude length) and radius of curvature of parallels R (longitude length) in WGS84 depend only on latitude phi and are defined as:

M = a(1 - e^2) / (1 - e^2 sin(phi)^2)^(3/2)

R = a cos(phi) / (1 - e^2 sin(phi)^2)^(1/2)

where

e is the eccentricity of the referenced ellipsoid of WGS84

a is the semi-major axis of the ellipsoid.

WGS 84 defines the semi-major axis of the WGS 84 ellipsoid a and the flattening factor of the Earth f as:

a=6378137m

1/f=298.257223563

With these defining parameters in WGS 84, we can obtain the eccentricity:

e=sqrt(2f-f^2)=0.081819191

Verification of the above equations

To verify whether the above eq. (1) and eq. (2) are correct, we will check them against the results at USGS which said, at 38 degrees North latitude, one degree of latitude equals approximately 364,000 feet (69 miles) and one degree of longitude equals 288,200 feet (54.6 miles).

To find the meridian radius of curvature M (latitude length) and radius of curvature of parallels (longitude length) at 38 degrees North latitude, we plug in a=6378137, e=0.081819191 and phi=38° into eq. (1) and eq. (2):

M = 6359629.652 m/rad

R = 5032429.322 m/rad

Furthermore, to find length of 1° of latitude and longitude, we multiply the above equations by the radian of 1°:

Length of 1° latitude = M * 1°/180° * pi = 6359629.652*1*pi/180 = 110996.4766 m = 68.970013 miles

Length of 1° longitude= R * 1°/180° * pi = 5032429.322*1*pi/180 = 87832.46103 m = 54.57656101 miles

The results are in line with the results of USGS.

Answer to your question

Repeat the above steps but plug in phi=37°15.8298′ = 37+15.8298/60=37.26383, we can obtain:

Length of 1° latitude=68.96139 miles

Length of 1° longitude=55.11761 miles

If I understand the 1/f =298.257223563 statement correctly, by transposing f= 298.257223563*1, or is this assumption incorrect?

Eventually I'd like to test the formulas to determine the lengths of 1° of Latitude and or Longitude. Any assistance would be greatly appreciated. On a side note. Supporters of Office 365 often say that the improvements under 365, significantly reduce the need to resort to VBA. Given that we are dealing with values with 9 decimals places can this overcome the limitation of floating point error, or is Office 365 still a fair way off this accuracy required?

Hi, everyone,

I am an VBA newbie to say at most. Please help me! I have searched through dozens of post, but nothing worked for me. Or maybe i don't know what to search

I have an excel workbook with more than 2 or 3 sheets. It's an schedule for the place i work at. We are working in shifts: 1st is from 7 to 15:30, 2nd is from 13:30 to 22:00 and 3rd is from 22:00 to 06:30.

As mentioned in the title, i need some code (or any idea) that will automatically replace 1, 2 or 3 with the above hour intervals. But with no macros.

I want it to happen in real time. I type 1, press Enter and poof!, 7:00/ 15:30 appears instead of 1. And so on for 2 and 3.

Thank you all !

]]>I am an VBA newbie to say at most. Please help me! I have searched through dozens of post, but nothing worked for me. Or maybe i don't know what to search

I have an excel workbook with more than 2 or 3 sheets. It's an schedule for the place i work at. We are working in shifts: 1st is from 7 to 15:30, 2nd is from 13:30 to 22:00 and 3rd is from 22:00 to 06:30.

As mentioned in the title, i need some code (or any idea) that will automatically replace 1, 2 or 3 with the above hour intervals. But with no macros.

I want it to happen in real time. I type 1, press Enter and poof!, 7:00/ 15:30 appears instead of 1. And so on for 2 and 3.

Thank you all !

I was coding and used a module name of numberFormat() and realized that it was already a default property of excel. Now, I changed the name of my module but Excel still mirrors the nameż

I even searched the whole workbook using CTRL + F and didn't find any other name usage other than the default property names.

Now, i cannot get my actual .NumberFormat property to work.

Also, I cannot upload the spreadsheet because it is too big. Any clue?

Thanks,

Daxton

]]>I even searched the whole workbook using CTRL + F and didn't find any other name usage other than the default property names.

Now, i cannot get my actual .NumberFormat property to work.

Also, I cannot upload the spreadsheet because it is too big. Any clue?

Thanks,

Daxton

hello all and thanks in advance

im trying to create a macro whereby a user can select a range of cells, runs a macro and a hyperlink is automatically to each cell. The hyperlink will be a combination of static text and whatever is in each cell.

below is what i have started - it is adding a hyperlink to all the cells, but the problem is the actual hyperlink URL is not updating for each subsequent cell and its contents - it is showing the same hyperlink for all cells based on the first active cell

thanks

Shaun

]]>im trying to create a macro whereby a user can select a range of cells, runs a macro and a hyperlink is automatically to each cell. The hyperlink will be a combination of static text and whatever is in each cell.

below is what i have started - it is adding a hyperlink to all the cells, but the problem is the actual hyperlink URL is not updating for each subsequent cell and its contents - it is showing the same hyperlink for all cells based on the first active cell

thanks

Shaun

Code:

`Sub Hyperlink()`

Dim newRange As Range

Dim newCell As Range

Set newRange = Selection

For Each newCell In newRange

With ActiveSheet

.Hyperlinks.Add Anchor:=newRange, _

Address:="pronto:%20/u/pronto/data/L25%20drilldown%20job-code%20" & ActiveCell.Text, TextToDisplay:=ActiveCell.Text

End With

Next newCell

End Sub

When I open my Excel 2021 file using a shortcut link to the xlsx file, it opens correctly.

When I open the file by double clicking on the actual xlsx file, all the cells are enlarged.

Same file just different ways of opening it.

]]>When I open the file by double clicking on the actual xlsx file, all the cells are enlarged.

Same file just different ways of opening it.

Hello,

In an Excel sheet, I would like to introduce a function (in text format) which will then be retrieved to be applied to various cells, but with the contents of a cell (which will vary dynamically) via the LET function.

For example: Function: x+x^(1/2) or sin(x+1) or 2x^3-3x^2+5*x-3 LET formula obtained by concatenation: LET(x, B40, x+x^(1/2)) or LET(x, B40, sin(x+1)) And then, to execute this function in different places, with B40 which will vary depending on the cell where LET is created: LET(x, C40, x+x^(1/2)) then LET(x, D40, x+x^(1/2))

But how to execute and calculate this function stored in text format?

The function =LET(x, B40, x+x^(1/2)) used directly in a cell gives a result, but if the function (here x+x^(1/2)) changes, all the LETs need to be rewritten...

While a dynamic composition allows you to have the right LET everywhere, but... how to execute it afterwards.

The VBA function gives me errors 2015.

I am open to any solution, even in VBA.

Thank you in advance for your help.

]]>In an Excel sheet, I would like to introduce a function (in text format) which will then be retrieved to be applied to various cells, but with the contents of a cell (which will vary dynamically) via the LET function.

For example: Function: x+x^(1/2) or sin(x+1) or 2x^3-3x^2+5*x-3 LET formula obtained by concatenation: LET(x, B40, x+x^(1/2)) or LET(x, B40, sin(x+1)) And then, to execute this function in different places, with B40 which will vary depending on the cell where LET is created: LET(x, C40, x+x^(1/2)) then LET(x, D40, x+x^(1/2))

But how to execute and calculate this function stored in text format?

The function =LET(x, B40, x+x^(1/2)) used directly in a cell gives a result, but if the function (here x+x^(1/2)) changes, all the LETs need to be rewritten...

While a dynamic composition allows you to have the right LET everywhere, but... how to execute it afterwards.

The VBA function gives me errors 2015.

I am open to any solution, even in VBA.

Thank you in advance for your help.

Hi!

I'm trying to check if cell in excel isn't a number.

But it gives me error.

What seems to be issue?

Thanks!

]]>I'm trying to check if cell in excel isn't a number.

But it gives me error.

What seems to be issue?

Code:

`Sub isNotNr()`

Dim input As Single

Dim WS As Worksheet

Set WS = ThisWorkbook.Worksheets("Sheet1")

input = WS.Range("B18").Value

Range("B18").Select

If input = 0 Or input < 0 Or IsNumeric(input) = False Then

MsgBox "Check input"

Exit Sub

End If

End Sub

Hi, the code below is being used to extract integer values from a string but I need the returned values to be formatted as TEXT with 4 characters i.e. "0000".

I'm new to Excel VBA so I'm not sure how to modify the code to do this.

I appreciate any help. Thank you

]]>I'm new to Excel VBA so I'm not sure how to modify the code to do this.

I appreciate any help. Thank you

Code:

Sub MID_VNUM()

Dim LastRow As Long

Dim i As Long

LastRow = Cells(Rows.Count, "B").End(xlUp).Row

For i = 3 To LastRow

Cells(i, 1).Value = Mid(Cells(i, 2).Value, 1, InStr(1, Cells(i, 2).Value, "-") - 2)

Next i

End Sub

Hi,everyone hope you are doing great

I want to transfer specific column data from my online excel to offline excel dynamic range.

In the screenshot, I have shown you online excel data and how I can transfer it offline to excel dynamic range automatically

for better understanding, I have attached a youtube video link for you.

https://www.youtube.com/watch?v=Sv1_eZYcd5Q

here I also attached an example video link what I want you can see

https://www.youtube.com/watch?v=V6dNzy1RATE

Can we can do this Excel with Vba code ?oh let suppose i transfer this online excel sheet to offline then run a macro to seperate a specfic columns

if you need further detail please tell me i will help you

I want to transfer specific column data from my online excel to offline excel dynamic range.

In the screenshot, I have shown you online excel data and how I can transfer it offline to excel dynamic range automatically

for better understanding, I have attached a youtube video link for you.

https://www.youtube.com/watch?v=Sv1_eZYcd5Q

here I also attached an example video link what I want you can see

https://www.youtube.com/watch?v=V6dNzy1RATE

Can we can do this Excel with Vba code ?oh let suppose i transfer this online excel sheet to offline then run a macro to seperate a specfic columns

if you need further detail please tell me i will help you