suresh's Profile

1690
Points

Questions
24

71

• Asked on January 11, 2019 in

Use this following solution :

The latest version of Excel, there is the syntax along commas, but not semicolons.

```=CONCATENATE(DEC2HEX(RANDBETWEEN(0,4294967295),8),"-",DEC2HEX(RANDBETWEEN(0,42949),4),"-",DEC2HEX(RANDBETWEEN(0,42949),4),"-",DEC2HEX(RANDBETWEEN(0,42949),4),"-",DEC2HEX(RANDBETWEEN(0,4294967295),8),DEC2HEX(RANDBETWEEN(0,42949),4))
```

Otherwise we can go for lower-cased according to this.

```=LOWER(CONCATENATE(DEC2HEX(RANDBETWEEN(0,4294967295),8),"-",DEC2HEX(RANDBETWEEN(0,42949),4),"-",DEC2HEX(RANDBETWEEN(0,42949),4),"-",DEC2HEX(RANDBETWEEN(0,42949),4),"-",DEC2HEX(RANDBETWEEN(0,4294967295),8),DEC2HEX(RANDBETWEEN(0,42949),4)))
```
• 1467 views
• Asked on January 11, 2019 in

Use this simplest solution to determine if a cell (value) is used in any formula :

Tools > Formula Auditing?

• 429 views
• Asked on January 11, 2019 in

The following command in the group of same objects, will explain about after installing from the below link,

“c:\Program Files\Softinterface, Inc\Convert XLS\ConvertXLS.EXE” /S”C:\MyExcelFile.xlsx” /F51 /N”Sheet1″ /T”C:\MyExcelFile.CSV” /C6 /M1 /V

Use this link, SoftInterface.com – Convert XLSX to CSV.

• 1048 views
• Asked on January 11, 2019 in

Try this best solution :

This (MsValue / 86,400,000) is used to Converting the value in milliseconds to days

By using DATE(1970,1,1)  to get 1/1/1970 as a numeric value.

```= (MsValueCellReference / 86400000) + DATE(1970,1,1)
```

To get date and time of 19/04/2010 08:16:11 by using the value of 1271664970687 and converting it as dd/mm/yyyy hh:mm:ss

• 625 views
• Asked on January 11, 2019 in

To ones that may used, the .Cells range is not restricted this much code is clearing the content of 1,048,576 rows and 16,384 columns – 17,179,869,184 total cells. Alternatively, clear the UsedRange.

```Sheets("Zeros").UsedRange.ClearContents
```

Another way to delete the sheet :

```Application.DisplayAlerts = False
Sheets("Zeros").Delete
Dim sheet As Worksheet
sheet.Name = "Zeros"
```
• 672 views
• Asked on January 11, 2019 in

Use this following command for Using EPPlus with a MemoryStream :

```using (var package = new ExcelPackage())
{

var stream = new MemoryStream(package.GetAsByteArray());
}
```
• 1258 views
• Asked on January 11, 2019 in

Just use this INDIRECT function, to use string value from a cell to access worksheet of same name :

```=INDIRECT("'"&A5&"'!G7")
```
• 457 views
• Asked on January 11, 2019 in

To flames a macro by using an event on the cell change.

Try this following command :

```vNew = Range("cellChanged").value
Application.EnableEvents = False
Application.Undo
vOld = Range("cellChanged").value
Range("cellChanged").value = vNew
Application.EnableEvents = True
```
• 686 views
• Asked on January 11, 2019 in

This solution is more excellent than using Interop objects.

```var package = new ExcelPackage(new FileInfo("sample.xlsx"));

ExcelWorksheet workSheet = package.Workbook.Worksheets[0];
var start = workSheet.Dimension.Start;
var end = workSheet.Dimension.End;
for (int row = start.Row; row <= end.Row; row++)
{ // Row by row...
for (int col = start.Column; col <= end.Column; col++)
{ // ... Cell by cell...
object cellValue = workSheet.Cells[row, col].Text; // This got me the actual value I needed.
}
}```
• 994 views
• Asked on January 11, 2019 in

Use this OFFSET method :

The column value is save in B1, and follow the below code,

```C1 = OFFSET(A1, 0, B1 - 1)
```

Running this methods :

a) Getting a base cell, that is A1.
b) preservation it as A and adding 0 to the row.
c) In the column  to adding ,A5 – 1.

In case want to modify the row value by using another value instead of 0.

• 1038 views