move cursor in cells (2000 sr 1)

Topic

New Reply

I get a value into sheet DATE with this piece of macro. I would want to add another controll in this mode:

GG = Mid(Sheets(“DATE”).Cells(2, 2), 1, 2)
MM = Mid(Sheets(“DATE”).Cells(2, 2), 4, 2)
AA = Mid(Sheets(“DATE”).Cells(2, 2), 7, 4)

I would want to add another var into code MESE and DECADE.
If the var MESE = 2 is refered to FEBBRAIO (Febrary) into sheet DATE (range A), if the var DECADE is “1” or “2” or “3” is refrered DECADE (range B, C, D)

i want similrar this

GG = Mid(Sheets(“DATE”).Cells(MESE , DECADE ), 1, 2)
MM = Mid(Sheets(“DATE”).Cells(MESE , DECADE ), 4, 2)
AA = Mid(Sheets(“DATE”).Cells(MESE , DECADE ), 7, 4)

how to make this?

Reply | Quote

Replies

Try

GG = Mid(Sheets(“DATE”).Cells(MESE + 1, DECADE + 1), 1, 2)

and similar for the other two.

Explanation:
MESE = 1 (Gennaio) corresponds to row 2, MESE = 2 (Febbraio) corresponds to row 3, etc.
DECADE = 1 corresponds to column B (the 2nd column), DECADE = 2 corresponds to column C (the 3rd column) etc.

Reply | Quote

WORK PERFECT!,
But how to understand me? I write with a terrible english!

is possible to use the same strategy for this:

I insert variuos value with this piece of code refered the sheet attached, in this code the range are fixed from C to E…
i would want to move based the cells into line 1 of sheet DECADE.

for eample assuming the var NUMERO_MESE = 2 select M2 and if the var NUMERO_DECADE = 3 select the range S to U…

‘SK-2001/SK-2002/SK-6002 – PRIMA DECADE

If Sess0.SCREEN.GETSTRING(8, 33, 1) = “_” Then
Sheets(“DECADE”).Range(“C” + RIGA) = “”
Else
Sheets(“DECADE”).Range(“C” + RIGA) = Sess0.SCREEN.GETSTRING(8, 33, 8)

End If

If Sess0.SCREEN.GETSTRING(9, 33, 1) = “_” Then
Sheets(“DECADE”).Range(“D” + RIGA) = “”
Else
Sheets(“DECADE”).Range(“D” + RIGA) = Sess0.SCREEN.GETSTRING(9, 33, 8)

End If

If Sess0.SCREEN.GETSTRING(10, 33, 1) = “_” Then
Sheets(“DECADE”).Range(“E” + RIGA) = “”
Else
Sheets(“DECADE”).Range(“E” + RIGA) = Sess0.SCREEN.GETSTRING(10, 33, 8)

End If

‘SK-2001/SK-2002/SK-6002 – PRIMA DECADE

Reply | Quote

You could use something like this:

Dim Colonna As Long
Colonna = 10 * Numero_Mese + 3 * Numero_Decade – 10

and instead of Range(“C” + RIGA), use Cells(Riga, Colonna)

Reply | Quote

No dubt you are a genius!

But to understand… why you have used *…
Is possible a little exaplain.
Tks

Reply | Quote

Each month is 10 columns to the right of the previous month, so we multiply the month number by 10.
Each decade within a month is 3 columns to the right of the previous decade, so we multiply the decade number by 3.
Finally, we have to subtract something to start in the correct column. For the first decade of January, 10 * mese + 3 * decade = 10 + 3 = 13. This decade starts in column 3, so we must subtract 10.

Reply | Quote

Tks Professor!

Reply | Quote

>>But how to understand me? I write with a terrible english!

He second-guesses you, Sal21. And most of the rest of us.
Look up the word ‘spooky’ in a dictionary?

Reply | Quote

Hans, sorry…

Fired!!!

Asuming have this new sheet MENSILE, wath is the formula refered COLONNA

Here is the old….

COLONNA = 10 * NR_MESE + 3 * NUMERO_DECADE – 10

Tks for patience.

Reply | Quote

There are no decades in this worksheet…

Reply | Quote

yes only a block of 4 column per Month…

Reply | Quote

Try COLONNA = 5 * NUMERO_MESE – 2

(together with the narrow separating column, each month takes up 5 columns. For NUMERO_MESE = 1, COLONNA must be 3 (column C), so you must subtract 2.

Reply | Quote

good, but if the var NUMERO_DECADE assuming = 1 i can use the old instruction COLONNA = 10 * NR_MESE + 3 * NUMERO_DECADE – 10 ???

Reply | Quote

No, because this worksheet has a different structure.

Reply | Quote

Clear me in statement select case…

I have this var NUM_PERIODO it can assuiming variuos number 01, 04, 06 eccc….

And i have this code, difficult to call the mcaro when the var assuming variuos vale, have maked this but not work….:
Private Sub CommandButton1_Click()
If ComboBox1.Value = “” Or ComboBox2.Value = “” Or ComboBox3.Value = “” Then
MsgBox “TUTTI I CAMPI SONO OBBLIGATORI!”
Exit Sub
End If
Unload Me
‘Select Case PERIODICITA
case is NUM_PERIODO = 4 Then
Call CICSEA_DECADE
case is NUM_PERIODO = 6 Then
Call CICSEA_MENSILE
‘End Select

End Sub

Reply | Quote

The Select Case statement should look like this:

Select Case NUM_PERIODO ‘ the name of the variable
Case 4
Call CICSEA_DECADE
Case 6
Call CICSEA_MENSILE
End Select

Reply | Quote

Tks, dubt….
The i have format the var NUM_PERIODO witrh “#00” in the case select is the same, for example 06 and 6?
the instruction intercept ths case always?

Reply | Quote

Is NUM_PERIODO declared as a numeric variable or as a string?

Reply | Quote

Global NUM_PERIODO As String

Reply | Quote

In that case, you can use

Select Case Val(NUM_PERIODO)
…
End Select

The Val function will convert both “6” and “06” to the numeric value 6, etc.

Reply | Quote

GOOD…
Tks.

Reply | Quote

WHY, WHY, WHY…..
When i use this formula if the num_dec assuming 3 the macro write in column 25 and not in 24

COLONNA = 11 * NUM_DEC – 8

in effect the var num_dec assume for trim1=1, trim2=2, trim3=3, trim4=4…

Reply | Quote

11 * 3 – 8 = 33 – 8 = 25

The numbers you added in row 1 are not the column numbers: column C = column 3, not column 2.

Reply | Quote

And now wath is correct?

Reply | Quote

As far as I can tell, COLONNA = 11 * NUM_DEC – 8 is correct.

Reply | Quote

Sorry…

Reply | Quote

My friend, have a prob related the famous: COLONNA = 5 * NUMERO_MESE – 2, ecc…
This function intercet where is the init to insert the value into column and cell…

Is possivble with this code, before to insert the vale, clear the block identified with:
COLONNA = 5 * NUMERO_MESE – 2

refer to the attached file, tks as usual.
Sal.

Reply | Quote

Did you intend to attach a workbook?

Reply | Quote

(No Text)

Reply | Quote

To clear the block in the column indicated by COLONNA:

Dim lngMaxRow As Long
lngMaxRow = Range(“A65536”).End(xlUp).Row
Range(Cells(4, COLONNA), Cells(lngMaxRow, COLONNA)).ClearContents

To clear the entire block of 4 columns starting with COLONNA:

Dim lngMaxRow As Long
lngMaxRow = Range(“A65536”).End(xlUp).Row
Range(Cells(4, COLONNA), Cells(lngMaxRow, COLONNA + 3)).ClearContents

Reply | Quote

Good, but before to start this macro i must select the sheet?

Reply | Quote

Either activate the sheet, or refer to the sheet in the code, for example:

Dim Foglio As Worksheet
Set Foglio = Worksheets(“MENSILE”)

lngMaxRow = Foglio.Range(“A65536”).End(xlUp).Row
Foglio.Range(Foglio.Cells(… etc.

Reply | Quote

WORK FINE!
But why … + 3

Reply | Quote

The second variant clears the cells in COLONNA plus the three columns to the right, i.e. all four columns for the specified month. So it clears from COLONNA to COLONNA+3.

Reply | Quote

Difficult with this to clear cells NUM_DEC it can assume 1, 2, or 3 how to mofify…

COLONNA = 10 * NUM_MESE + 3 * NUM_DEC – 10

Reply | Quote

The code is exactly the same if you want to clear one column.

Reply | Quote

Assuming i use this sheet (attached into first thred of this post move_by_month.xls) possible to insert a button to:

1) Show a userform

2) In this userform insert 3 Listbox

3) list box 1 – Listing the value into column A range A2:A13 (or fill with the normal month) and assign to the var NUm_mese the selected value from the list box in this mode:
if i select Febbraio from list assign to the var NUm_mese “02”, if i select Marzo from list assign to the var NUm_mese “03….ecc..

4) list box2 – is filled with the related decade value in refrence to the month:

Example:

I selct into list box 1 i select the month Febbraio listing the date of decade

10/02/2005 (is 1 DECADE, assign the value 1 to the var Num_decade)
20/02/2005 (is 2 DECADE, assign the value 2 to the var Num_decade)
28/02/2005 (is 3 DECADE, assign the value 3 to the var Num_decade)

In this case not is possible to make error when is selected the the decade from month

5) list box 3 – listing a value similar TIPO01, TIPO02, TIPO03 ecc… and assign to the value selected a value into var Num_Tipo the numerical value TIPO01 var Num_Tipo =”01” ecc…

Tks.

Reply | Quote

Yes, it is possible, but you will have to make the userform yourself, Sal.

Reply | Quote

Hi Hans, good morning…
have mked a test… (3 Hours…)

Difficult to assign to the var Into combo box 3 the real value of various item… example for 01-GIORNALIERA assign 01, for 04-DECADALE assign 04, ecc….

01-GIORNALIERA
04-DECADALE
05-QUINDICINALE
06-MENSILE
08-TRIMESTRALE
10-SEMESTRALE
11-ANNUALE

Reply | Quote

Use Left(ComboBox3, 2) to extract the first two characters.

Reply | Quote